One column of full names, two columns out: first name in one, surname in the other. This takes the text on either side of the space.
The formula
=TEXTBEFORE(A2," ") in column B
=TEXTAFTER(A2," ") in column C
What each part does
TEXTBEFORE
Splits the text at the character you name and keeps the left side.
" "
The character to split on, here a space. Put whatever separates your text in the quotes: a comma, a slash, a dash.
How it works, and what to watch for
TEXTBEFORE and TEXTAFTER want Microsoft 365. On older Excel the same split needs LEFT and RIGHT paired with FIND, which is longer and easier to get wrong.
A middle name upsets the simple version: for "Anne Marie Lee" the surname side would pick up "Marie Lee" rather than just "Lee". When there are three parts, split on the first space and the last space separately.
Flash Fill (Ctrl+E) can do this too, but it does not follow along when the names change later. A formula does.
Questions
How do I handle a middle name?
Take the first name with TEXTBEFORE up to the first space, and the surname with TEXTAFTER the last space. Whatever sits between them is the middle name.
A formula or Text to Columns?
Text to Columns is a one-off split. A formula re-splits on its own whenever the names change, which is usually what you are after.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
