The formula
First name (fill down)ExcelGoogle Sheets
=LEFT(A2,FIND(" ",A2)-1)In C2 → Ana
Last name (fill down)ExcelGoogle Sheets
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))In D2 → Diaz
"Last, First" to "First Last"ExcelGoogle Sheets
=TRIM(MID(A4,FIND(",",A4)+1,100))&" "&LEFT(A4,FIND(",",A4)-1)In C4 → Dev Rao
Excel 365Excel 365
=TEXTSPLIT(A2," ")In E2, spills: [Ana, Diaz]
✓ Tested. All 5 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Excel 365 was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Full name | |||||
| 2 | Ana Diaz | Ana | Diaz | Ana | Diaz | |
| 3 | Mary Ann Lee | Mary | Lee | |||
| 4 | Rao, Dev | Dev Rao |
How it works
- FIND(" ",A2)-1
- Characters before the first space.
- SUBSTITUTE/REPT/RIGHT/TRIM
- Isolates the last word.
- FIND(",",A4)
- Position of the comma in "Last, First".
Common errors and fixes
Why does the formula return #VALUE!?
Single-word name with no space. Wrap in IFERROR(…,A2).
Middle names?
"Mary Ann Lee" → first "Mary", last "Lee". Middle = TRIM(MID(A3,LEN(C3)+1,LEN(A3)-LEN(C3)-LEN(D3))).
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.