📎 This article includes 1 downloadable practice file ↓
Customer and employee lists often keep full names in one cell, but mail merges, sorting by surname and CRM imports need them split. Names are messy — some have middle names, some have only one name, some have double spaces — so the formula has to cope.
In this article
Excel 365: TEXTBEFORE and TEXTAFTER
=TEXTBEFORE(TRIM(A2), " ") ' first name
=TEXTAFTER(TRIM(A2), " ", -1) ' last name (after the LAST space)
=TEXTBEFORE(TEXTAFTER(TRIM(A2), " "), " ", -1, , , "") ' middle name(s), blank if none
=TEXTSPLIT(TRIM(A2), " ") ' every part in its own columnTRIM first removes double and trailing spaces, which otherwise create empty “names”. The -1 means “count from the end”.
=TEXTBEFORE(TRIM(A2)," ",,,,TRIM(A2)) returns the whole name instead.Any version: LEFT, RIGHT, FIND
=LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ") - 1) ' first name
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100)) ' last nameThe last-name trick replaces every space with 100 spaces, takes the right-most 100 characters (which now contain only the last word plus padding) and trims the padding.
No formulas: Flash Fill and Text to Columns
- Flash Fill: type the first name of row 2 in B2, press Ctrl+E. Repeat for surnames. Fast, but doesn’t update when names change.
- Text to Columns (Data tab): split on spaces. Works for two-part names; three-part names spill into an extra column.
Proper case after splitting
=PROPER(TEXTBEFORE(TRIM(A2)," ")) turns “ASHA” into “Asha”. Check exceptions like “D’Souza” and “McDonald” by eye.
Where people go wrong
| Problem | Fix |
|---|---|
| Empty first name | Leading space — always TRIM first |
| Surname includes the middle name | Used the FIRST space instead of the last — use -1 or the REPT trick |
| #VALUE! on single names | FIND found no space — append &" " inside FIND |
| Titles like “Dr.”, “Mr.” become first names | Remove them first with SUBSTITUTE, or a lookup list of titles |
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.