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.

    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


    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.


Office Tips 001: Excel, Find Unique Value

The following shows the fastest way (probably!) to filter out the unique values in a list that contains duplicates, and to count the number of occurrences of each unique value.

(Excel Version: Office 2013).

Find Unique Values

  1. Select the data to be filtered.


    Fig 1. A column of names.

  2. Go to DATA tab, in the Sort & Filter section, click on Advanced.


    Fig 2. Select filter tool.

  3. In the Advanced Filter options,
    – Select Copy to another location
    – Select the cell to copy the unique values to, using the Copy to: option
    – Check Unique records only
    – Click OK


    Fig 3. Adjust Advanced Filter options.

  4. Ta-Da! A list of unique values will appear at the cell that you have selected.


    Fig 4. Unique values output.

Number of occurrences for each value

For this to work, you will have to modify the data sheet a little bit and make use of a pivot table.

  1. Create a column of a single value “1” beside the column of data.


    Fig 5. Add a count beside each value.

  2. Go to INSERT tab, in the Tables section, click on PivotTable.


    Fig 6. Select table tool.

  3. In the PivotTable options,
    – Under Choose the data that you want to analyze,
    – Select Select a table or range
    – Select both column of data, using Table/Range: option
    – Under Choose where you want the PivotTable report to be placed,
    – Select Existing Worksheet
    – Select a cell to output the results, using Location: option


    Fig 7. Adjust PivotTable options.

  4. Go to PivotTable Fields that appear on the right of the window,
    – Under Choose fields to add to report: option,
    – Check both check-boxes that belong to the header of your data columns


    Fig 8. Display PivotTable fields.

  5. There! You now have a pivot table with the number of occurrences for each value in your data.


    Fig 9. PivotTable output.

These are definitely not the most elegant solutions, but it will solve your immediate problems.