Splitting first and last name from one column

Your address list has "Jan de Vries" in a single cell, but for a mailing you want "Dear Jan". So you need the first name on its own. There are two ways to do this.

Method 1: Text to Columns

1. Make sure there are enough empty columns to the right of the names. Otherwise, existing data will be overwritten.
2. Select the cells with names.
3. On the Data tab, click Text to Columns.
4. Under Delimiter, tick only Space and check the Preview to see the result. Click OK.

The downside: with "Jan de Vries", "de" and "Vries" end up in separate columns, and with "Willem van der Berg" you get four columns.

Method 2: with formulas (better for names with prefixes)

If the name is in C2:

• First name: =LEFT(C2,FIND(" ",C2)-1). Everything up to the first space.
• The rest (prefix and last name): =MID(C2,FIND(" ",C2)+1,100). Everything after the first space, so "de Vries" and "van der Berg" stay intact.

Drag both formulas down. If you want to remove the formulas, copy the columns and paste them as values (right click, Paste special).

Tip: to do the reverse, combining first and last name, use =A2&" "&B2 or =TEXTJOIN(" ",TRUE,A2:B2).

Pitfalls

• Double first names ("Anne Marie de Groot") and names without a space can't be split flawlessly by any method. Check the result carefully.
• If there's no space in the cell, FIND returns an error. Catch this with IFERROR: =IFERROR(LEFT(C2,FIND(" ",C2)-1),C2).