How to split first and last names in Excel 2013

Download Excel File

Conventionally, the "convert text to columns" wizard was generally used to split first name and last name in earlier versions of Excel.

A new feature called "Flash Fill" has been introduced in Excel 2013 to perform such task. Flash Fill detects a pattern in your initial data entry which enables it to figure out remaining data you want to enter

The main advantage of using Flash Fill is that there is no need of any formula, wizard or any kind of construct to get the desired output.

Suppose we have full name of the person in column A. We are planning to split this name as below:
First name – Column B
Last name – Column C

Following steps will help to split first and last names into separate columns:

1. Enter the first name in cell B1 and press ENTER
2. Start typing the next one and instantly the "Flash Fill" feature will show the remaining list of first names
3. Hit ENTER to accept the suggested list OR press "Esc" to continue typing remaining names
4. In the similar way, initiate to type Last name in cell C1 and press ENTER
5. Again, Flash Fill will give suggested entry once you initiate typing in second entry
6. Press ENTER to accept suggestion from Flash Fill and it's done

Important note: Flash Fill feature is case sensitive. If you type lower case for initial entry, all the suggested entries will be in the same case. This is as such one more advantage over using "convert text to columns" wizard.

Bonus tip: If Flash Fill is turned on but doesn't start automatically when you type data that matches a pattern, you can try starting it manually by clicking Data >>> Flash Fill, or by pressing Ctrl+E.