Splitting text used to mean complicated formulas with LEFT, FIND and MID. Newer versions of Excel have three functions that make it simple: TEXTBEFORE, TEXTAFTER and TEXTSPLIT.
Works in: Excel for Microsoft 365, Excel 2024 and Excel for the web.
TEXTBEFORE: everything before a character
=TEXTBEFORE(A2, " ")| A2 | Formula | Result |
|---|---|---|
| Sara Ali | =TEXTBEFORE(A2," ") |
Sara |
| sara@company.com | =TEXTBEFORE(A2,"@") |
sara |
| INV-2026-0042 | =TEXTBEFORE(A2,"-") |
INV |
TEXTAFTER: everything after a character
=TEXTAFTER(A2, "@")| A2 | Formula | Result |
|---|---|---|
| sara@company.com | =TEXTAFTER(A2,"@") |
company.com |
| Sara Ali Hassan | =TEXTAFTER(A2," ",-1) |
Hassan (after the last space) |
| INV-2026-0042 | =TEXTAFTER(A2,"-",2) |
0042 (after the 2nd dash) |
Tip: the third argument chooses which occurrence. Negative numbers count from the end, so -1 means the last one.
TEXTSPLIT: split into separate cells
=TEXTSPLIT(A2, ", ")If A2 is Red, Blue, Green, the result spills into three cells: Red | Blue | Green.
- Split into rows instead of columns:
=TEXTSPLIT(A2,,", ")(leave the column delimiter empty). - Several delimiters:
=TEXTSPLIT(A2,{",",";"," "}) - Ignore empty values: set the 4th argument to TRUE:
=TEXTSPLIT(A2,",",,TRUE)
Make sure the cells to the right are empty, or you’ll get a #SPILL! error. See Excel errors explained.
Handle missing delimiters
If the text doesn’t contain the delimiter, TEXTBEFORE/TEXTAFTER return #N/A. Add a fallback with the last argument:
=TEXTBEFORE(A2," ",,,,A2)This returns the whole text when there’s no space (for one-word names).
Practical examples
| Task | Formula |
|---|---|
| First name | =TEXTBEFORE(A2," ") |
| Last name | =TEXTAFTER(A2," ",-1) |
| Email domain | =TEXTAFTER(A2,"@") |
| Text inside brackets | =TEXTBEFORE(TEXTAFTER(A2,"("),")") |
| File extension | =TEXTAFTER(A2,".",-1) |
Clean extra spaces first with TRIM. For more name examples, see how to split first and last names.
Older Excel and Google Sheets
- Older Excel: use Data → Text to Columns, Flash Fill (Ctrl + E), or LEFT/FIND formulas:
=LEFT(A2,FIND(" ",A2)-1). - Google Sheets: use
=SPLIT(A2,",")or REGEXEXTRACT. See the Google Sheets cheat sheet.
Quick summary
- TEXTBEFORE = text before a character. TEXTAFTER = text after it.
- Use -1 for the last occurrence (great for last names and file types).
- TEXTSPLIT splits text into multiple cells or rows.