How to Separate First and Last Name in Excel (4 ways)

Full names in column A, and you need first and last names in their own columns.

1. Text to Columns (fastest one-off)

1. Make sure columns B and C are empty.
2. Select column A › Data › Text to Columns.
3. Delimited › Next › tick Space › Next.
4. Destination: $B$1 (so the original stays) › Finish.

People with middle names spill into a third column. More: split a cell.

2. Flash Fill

Type “Asha” in B2, press Ctrl+E in B3. Type “Rao” in C2, Ctrl+E in C3. See Flash Fill.

3. Formulas (update when names change)

First name:

=LEFT(A2,SEARCH(" ",A2)-1)

Last name (everything after the first space):

=MID(A2,SEARCH(" ",A2)+1,100)

Last name when there’s a middle name (after the last space):

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

4. Excel 365: TEXTBEFORE and TEXTAFTER

• First: =TEXTBEFORE(A2," ")
• Last: =TEXTAFTER(A2," ",-1) (-1 = the last space)
• All parts at once: =TEXTSPLIT(A2," ") — see TEXTSPLIT

“Rao, Asha” format

First: =TEXTAFTER(A2,", ") · Last: =TEXTBEFORE(A2,",").

Before you start

Double spaces break every method. Clean first with =TRIM(A2). See remove spaces.

The reverse: combine first and last name