6. Splitting first names and surnames in Excel
In this lesson you learn how to split names automatically.
Splitting names
Recreate the table below:
Figure 6.1 Names
In B2, type the first name of the first person: Marian and press Enter.
In B3, type the first name of the second person: Joris. If Excel recognises the name, press Enter.
Figure 6.2 First names
Figure 6.3 First names
Figure 6.4 Surnames
Not working? Use Ctrl+E
Excel calls this Flash Fill (Flash Fill). If it doesn't fill in by itself, type the first two names yourself and press Ctrl+E. You'll find the same button on the Data tab as well, in the Data Tools group.
Watch out for name prefixes
With Dutch names, Flash Fill often goes wrong on name prefixes. With "Jan van der Berg", Excel doesn't know whether "van der" belongs to the first name or to the surname. Always check those rows afterwards, or give Excel more examples: type three or four yourself first and only then fill in the rest. The more examples, the better it recognises the pattern.
The second method: Text to Columns
If all the names are neatly separated by a space, this is quicker:
- Select the column with the full names.
- Go to the tab Data and click Text to Columns.
- Choose Delimited and click Next.
- Tick Space and click Next.
- Under Destination, select an empty column, otherwise you will overwrite your original data.
This method splits at every space. So with "Jan van der Berg" you end up with four columns instead of two. It works fine for a list of simple names, but not for a list with name prefixes.
The third method: with a formula
If you want the split to update when you change the names, use formulas. If the full name is in A2:
- First name:
=LINKS(A2;VIND.SPEC(" ";A2)-1) - Surname:
=RECHTS(A2;LENGTE(A2)-VIND.SPEC(" ";A2))
The first formula takes everything before the first space, the second everything after it. Here too, everything after the first space ends up in the surname, including the name prefix. For most address files that is exactly right.
What you can do now
There are three ways to split a column of full names: with Flash Fill for a one-off job, with Text to Columns for a large, tidy list, and with formulas if the result needs to update automatically.