Office Tips 002: Excel, Split and Merge Data

The following shows how to split a column of data into multiple columns by specifying the delimiters within the data, and subsequently how to merge multiple columns of data back into a single column.

This was useful for me when I wanted to sort a list of IP addresses. By default, Excel will recognise the addresses as a string instead of numbers, therefore it is unable to sort the IP addresses in the correct order. In this example, I will use IP addresses to demonstrate this method of manipulating Excel data.

(Excel Version: Office 2013)


Splitting a column of data into multiple columns

  1. Select the data to be worked on. Make sure that there are empty columns next to the data.

    select data.PNG

    Fig 1. Column of IP addresses

  2. From the DATA tab, in the Data Tools section, click on Text to Columns.

    select text to column.PNG

    Fig 2. Select Text to Column tool

  3. From the pop-up wizard, you may specify the various requirements of splitting the data. You may specify the types of delimiters to split the data and the format of the output, among other options. In this example, I have split the IP address by the period character.
    Wizard1.PNG
    Wizard2.PNG
    Wizard3.PNG

    new columns.PNG

    Fig 3. Data split into multiple columns

     

And with that we are done! Bonus: You may now manipulate the data, which cannot be done in the original format. In this example, I have sorted the IP address in ascending order using the sort tool.

sorted column.PNG

Fig 4. IP addresses sorted in order


Merging multiple columns of data into a single column

  1. Now to merge the data into a single column. Begin in an empty cell in the first row and use the following formula. In this example, I have started from a cell in column F and concatenated all the values of the other cells in the row, with the “.” period character in between each value.
    merge one.PNG

    Fig 5. Merging of data

    formula.PNG

    Fig 6. Formula

  2. With one row of data done, simply replicate the formula to all subsequent rows by pulling the selected cell.

    merge all.PNG

    Fig 7. Merging all rows of data

  3. To decouple the merged data from the individual input columns of the formula, you can simply copy the output column and paste it wherever desirable by using the Paste Values from the Paste Options.

Now we have a list of nicely sorted IP addresses! If you would like to automate this further, you may explore recording a simple macro for these steps.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s