Splitting first name and surname from one column

Your address list has "Jan de Vries" in one 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. Click Text to Columns on the Data tab.
4. Under Delimiter, tick only Space and check the Preview to see the result. Click OK.

Drawback: 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 surname): =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 get rid of the formulas, copy the columns and paste them as values (right-click, Paste special).

Tip: to do the reverse, joining first name and surname together, use =A2&" "&B2 or =TEXTJOIN(" ",1,A2:B2).

Pitfalls

• Double first names ("Anne Marie de Groot") and names without a space can't be split flawlessly by any method. Be sure to check the result.
• If a cell doesn't contain a space, FIND returns an error. Catch it with IFERROR: =IFERROR(LEFT(C2,FIND(" ",C2)-1),C2).