Published

Office

Excel: Split First and Last Names

Three ways to split 'Jane Smith' into two columns, from easiest to most flexible.

1. Flash Fill

Type the first name for row 1 in the next column, start row 2 and press Ctrl + E.

Ctrl + E

2. Text to Columns

Select the names > Data > Text to Columns > Delimited > Space > Finish.

Data > Text to Columns > Space

3. Formulas (Microsoft 365)

Update automatically when names change.

=TEXTBEFORE(A2," ")
=TEXTAFTER(A2," ")

FAQ

What about middle names?

TEXTAFTER(A2," ",-1) gets the last word, so 'Mary Ann Smith' gives Smith.

TEXTBEFORE isn't available?

It's in Microsoft 365 and Excel 2024. In older versions use Flash Fill or Text to Columns.