Splitting columns of data up in Excel (and Flash Fill)

What happens when you have a spreadsheet like this…

A spreadsheet with a Full Name column the needs splitting in to 2 columns one for First Name and one for Surname

And you want to split the first column (Full Name) into 2 separate columns, like this…

What the end result of Text to Columns may look like in this example

How do you do that, the easy way?

First of all, you need to create 2 new columns…

Inserting 2 new columns in Excel

Then, name those two new columns…

Entering 2 new labels in the new columns

Now you need to highlight the data in the column you wish to split up…

Highlighting the first column of data

Select the command Data – Text to Columns

Selecting Data - Text to Columns

In the dialog box that appears, select Delimited (if, as in this case, it is an identifiable character that separates the two words you want to split up).

You would used Fixed if you were splitting data on the basis of the first part of the data being of a fixed length in every case, (i.e. a code number of say, 3 characters)…

Deciding how the data should be split in Text to Columns

In the next part of the wizard, you show Excel which character it is that separates the two things you want to separate (in this case there is a space between the first name and surname)…

Selecting the delimiter in Text to Columns

In the final part of the wizard, you decide on a format for the data in the new columns and you also need to tell Excel which cell the new columns of data start from…

The final dialog box where you decide on format and the start cell of the spilt data

Now you will see your 2 new columns of data successfully split up from the initial first column…

End result of splitting columns with Text to Columns

Here is a video that shows you the full process above:

What if you want to do the reverse?

In other words, you have two columns with first name and surname, and you want to create a single column with Full Name in, and don’t want to have to enter all of text again?

You can use an Excel thing called Flash Fill

Leave a Reply

Your email address will not be published.