Split a Cell in Excel: one column into two

“Ana Roy” in one cell, but you need First and Last in two. Three ways — pick by how often you will do it.

Way 1: Text to Columns (one-off)

1. Select the cells to split (A1:A50). Make sure the columns to the right are empty — the results go there.
2. Data › Text to Columns.
3. Choose Delimited › Next.
4. Tick Space (or Comma, or Other for a dash). Untick the rest. Next.
5. Click Finish.

Column A now holds first names, column B last names. The original is replaced — copy it first if you need it.

Way 2: Flash Fill (fastest)

1. In B1 type the first name by hand: Ana.
2. Click B2 and press Ctrl + E.

Excel fills the whole column by copying your pattern. Do the same in C1/C2 for last names.

Way 3: a formula (updates itself)

Excel 365:

=TEXTSPLIT(A1, " ")

Older Excel, first word and the rest:

=LEFT(A1, FIND(" ", A1)-1)
=MID(A1, FIND(" ", A1)+1, 100)

Check it worked

Scroll to a name with a middle name. Text to Columns makes three columns; Flash Fill and the formulas above put everything after the first space in the second column.

Common mistake

Running Text to Columns with data in the next column — Excel overwrites it. It warns you; click Cancel and insert empty columns first.