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

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.

    Capture3.PNG

    Fig 1. A column of names.

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

    Capture4.PNG

    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

    Capture5.PNG

    Fig 3. Adjust Advanced Filter options.

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

    Capture6.PNG

    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.

    Capture7.PNG

    Fig 5. Add a count beside each value.

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

    Capture8.PNG

    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

    Capture9.PNG

    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

    Capture10.PNG

    Fig 8. Display PivotTable fields.

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

    Capture11.PNG

    Fig 9. PivotTable output.


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