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.

Using Flash Fill

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

Flash Fill live preview

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

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

Using Flash Fill

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

Using Flash Fill

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

Using Flash Fill

Capitalization

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

Using Flash Fill

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.

Using Flash Fill

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.