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
- Select the data to be worked on. Make sure that there are empty columns next to the data.
- From the DATA tab, in the Data Tools section, click on Text to Columns.
- 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.
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.
Merging multiple columns of data into a single column
- 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.
- With one row of data done, simply replicate the formula to all subsequent rows by pulling the selected cell.
- 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.