Split first and last name in one cell

text cleanup names

Formulas that take the first and last word from a full name, including a middle name.

The problem

A full name is in one cell and you need first and last separately.

Formulas

First: =LEFT(H2,FIND(" ",H2&" ")-1)                                -> Ana
Last:  =TRIM(RIGHT(SUBSTITUTE(H2," ",REPT(" ",50)),50))             -> Lopez (Bo Q Chen gives Chen)

The first formula also works when there is no space. The last formula takes the final word.

Excel vs Sheets

Same in both.

Tested on sample data by the Cellwise checker (log CW-PL-20261018-41). AI-made: the text, voice and visuals are generated by AI.

Checked before publishing: a check.