How to / Text

How to split first and last name in Excel

For the first name, use =TEXTBEFORE(A2, " "); for the last name, =TEXTAFTER(A2, " "). Both split at the first space.

Everything before the first space

ABC
1Full name
2Ana SilvaAna
3Ben Lee Jones
4Cher

In C2: =TEXTBEFORE(A2, " ") → Ana

How it works

  1. TEXTBEFORE returns the part of the text that comes before a separator: here, a space.
  2. TEXTAFTER returns the part that comes after it.
  3. Both need Microsoft 365. The versions below work everywhere.

Variations

  1. The last name

    ABC
    1Full name
    2Ana SilvaSilva
    3Ben Lee Jones
    4Cher

    In C2: =TEXTAFTER(A2, " ") → Silva

  2. The last name of someone with three names

    ABC
    1Full name
    2Ana SilvaJones
    3Ben Lee Jones
    4Cher

    In C2: =TEXTAFTER(A3, " ", -1) → Jones

    The -1 counts spaces from the end, so only the last word is returned.

  3. First name, in any version of Excel

    ABC
    1Full name
    2Ana SilvaAna
    3Ben Lee Jones
    4Cher

    In C2: =LEFT(A2, FIND(" ", A2)-1) → Ana

    FIND gives the position of the space; LEFT takes the characters before it.

  4. Last name, in any version of Excel

    ABC
    1Full name
    2Ana SilvaSilva
    3Ben Lee Jones
    4Cher

    In C2: =MID(A2, FIND(" ", A2)+1, LEN(A2)) → Silva

  5. Both at once, in two cells

    ABCD
    1Full name
    2Ana SilvaAnaSilva
    3Ben Lee Jones
    4Cher

    In C2: =TEXTSPLIT(A2, " ") → Ana, Silva

    The pieces spill into the cells to the right.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related