Fix Attendee List Split Data Into Columns

by Sarah Stoski · Published August 17, 2022 · Updated August 21, 2022

When you download an attendee list for your upcoming event, the Excel file might have all the data in one column. See how to use the Excel Flash Fill shortcut, to quickly separate that data into separate columns, so you can sort and filter it later.

Video: Quick Tip to Split Names

In this short video, column A has messy data – full name, age and city, all lumped together. Unfortunately, this is how we get our data sometimes, and we have to clean it up!

I’ll show you a quick and easy way to separate that data into multiple columns, using an Excel shortcut.

There are written steps below the video.

Messy Data to Fix

In the video, column A had attendee data, for people coming to our conference.

I call it “Messy Data”, but there is a consistent pattern to the way the data is entered in each cell.

There are four pieces of information in each cell
Items are separated by space characters

First name
Last name
Age
City

To work with that data, I need to put each piece of information in a separate column on the worksheet.

Add Column Headings

First, I’ll put 4 new headings on the worksheet, where I want the pieces of information to go.

The messy data heading is in cell A2, so I put the following headings in the same row:

B2 – First Name
C2 – Last Name
D2 – Age
E2 – City

Enter Items in First Row

In a minute, I’ll use a shortcut to quickly split the data into separate columns

But first, I have to show Excel what the pattern is. I’ll enter the separate items from cell A3, in the four columns to the right:

B3 – Jane
C3 – Devine
D3 – 44
E3 – Toronto

Flash Fill Shortcut

Now, the fun part – follow the steps below, to quickly split all the remaining rows of messy data.

Select cell B4 – it is the first blank cell in the next row
Then, press Ctrl + E – that is the Flash Fill shortcut
Press the Tab key on your keyboard, to move to cell C4
Press Ctrl + E again, to Flash Fill the last names.
Finally, repeat those steps in column D and column E, to flash fill the Age and City for each row.

That’s it! In just a few seconds, you split each row’s combined data into four separate columns!

____________________