Excel – Flash Fill to separate first and last names in cells

Excel – Flash Fill to separate first and last names in cells

Mark June 1, 2020

I have had this a number of times through my IT career where I have exported a list of names with both the First and Last name in the same cell… but I needed them to be is separate rows.

I would then spend the next hour manually typing the First name … into the first name column and …. then the Last Name into…. you guessed it … the Last Name column *Facepalm*

I would always try to use the “Convert Text to Columns” option, but often it would not work and I would spend time troubleshooting this only to finally go back the manually typing the names in … but no longer thanks to “Flash Fill” in Excel: –

In this example, a spreadsheet has the full name in 1 cell and the mobile in another and need to separate the first and last name into separate cells.

Add 2 new columns (first and last/second name), and enter the first person name in (Will in the first name, Bank in the last name), then click in the below cell under Will (the first name)

Click on “Data” and press “Flash Fill”

This will automatically fill in the First names for the remaining users, repeat for the last name.

I don’t think I have ever been as excited about an Excel tool … 🙂