Skip to content
Klargrid

Split first and last names in Excel

One column "Mary Smith", and you need two. Three ways to do it in Excel, and one case that defeats them all: compound names.

In Excel, step by step

  1. With Text to Columns

    Select the column, Data, Text to Columns, Delimited, tick Space. Two columns, first and last name.

  2. With Flash Fill

    Type the first first name next to it, then Ctrl+E: Excel guesses the rule and fills the column.

  3. With a formula

    =LEFT(A2,FIND(" ",A2&" ")-1) for the first name, =TRIM(MID(A2,FIND(" ",A2&" ")+1,LEN(A2))) for the last name.

Pitfalls

  • "Mary Ann Smith": Text to Columns makes three columns, and you no longer know where the first name ends.
  • "Anna van der Berg": the last name starts at the second word, not the last.
  • Double spaces make empty columns: TRIM first.
  • Text to Columns overwrites the neighbouring columns without warning.

Faster, on the whole file

Split or merge columns cuts a whole column at the first space, the last one, or every separator, shows the result before writing, and overwrites nothing. For a formula to paste: Find the right formula, "extract the first name".

Frequently asked questions

And to join first and last names?
=TEXTJOIN(" ",TRUE,A2:B2) joins them with a space, without a double space when one is missing.