Split a column of data based on what you type

You need to separate first and last names (or part names and numbers, or any other data) into separate columns. A new Excel 2013 feature called Flash Fill can help.

Enter the first name or other value in the column next to your data and press Enter.

Start typing the next name. Flash Fill will show a list of suggested names.

If it looks good, just press Enter to accept the list.

Tip:  To continue typing without using suggested names, press Escape.

Now enter a last name in the next column, and press Enter.

Start typing the next name, press Enter, and you're done.

Capitalization

Flash Fill is case sensitive. If you enter a lower-case last name, for example, all the last names follow suit.

If you don't like a result, just press Escape, and continue typing without using suggestions.

You can also make case sensitivity work for you, and use Flash Fill to change the case of text.

Dealing with middle names

If your data consistently has middle initials or names (meaning every name has one), Flash Fill works the same way, but you follow the steps three times instead of two.

More about Flash Fill

Your data doesn't have to be names, of course. Flash Fill works with any data you need to split into more than one column, or you can simply use it to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data.

However, Flash Fill may not always start filling out your data. It works best when your data has some consistency. For example, all names have middle initials or all addresses use the same type of postal codes. If your data has many inconsistencies, you can always use Text to Columns to split text into different cells, or use functions to split text.