Glide Training Logo

Splitting Text Into Separate Columns

  

Written by Jane Hames

Lists of data are often received in a format that is not ideal for the work that needs to be carried out with it. One example of this is when you have first name and surname in one cell, where you really need the information to be in two separate cells.

A quick and easy (when you know how) solution is the Text to Columns feature and the following tutorial describes how to do it.

    1. Start with data something like this. Here, I want the town and county to be in separate cells:

excel splitting text into columns

    1. Select the cells in the column to be split.
    2. From the Data tab, click on the Text To Columns button.

excel splitting text into columns

    1. If the split is to happen where there is a space, comma, tab or other character, then choose Delimited. If the split is to happen at a fixed point in each cell, then select Fixed Width
    2. Click on the Next button.

excel splitting text into columns

    1. For delimited text, select the Delimiter. For fixed width, click where the split should be.
    2. Click on the Next button.

excel splitting text into columns

    1. Select the Destination cell (this should the starting point for the text once it has been split, a bit like you would do when preparing to paste copied data).
    2. Click in Finish and this is what I end up with, all ready for me to be able analyse my customers by town and by county:

excel splitting text into columns

 


previous post: One space or two? A clever use of Find and Replace in Word

next post: A Clever Use of Slicers in an Excel Pivot Table