How to split text in Excel with a formula
TEXTBEFORE returns everything before a marker, and TEXTAFTER returns everything after it. They work however long each piece is.
TEXTSPLIT splits at every marker and spills each piece into its own cell, like Text to Columns that updates itself.
The formula
| A | |
|---|---|
| 1 | Full name |
| 2 | Amara Okafor |
=TEXTBEFORE(A2," ")Result: Amara
Returns the text before the first space, which is the first name.
Step by step
- Type =TEXTBEFORE(A2," ") for the part before the space.
- Type =TEXTAFTER(A2," ") for the part after it.
- Or type =TEXTSPLIT(A2," ") once, and let it spill both parts.
Other ways to write it
=LEFT(A2,FIND(" ",A2)-1)The older way, which works in every version of Excel.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Middle names
- TEXTAFTER(A2," ") returns everything after the first space, middle name included. Use -1 as a third argument to split at the last space.
- Something in the way of a spill
- TEXTSPLIT needs empty cells to spill into. If they aren’t, you’ll see #SPILL!.
Reading isn’t the same as doing
TEXTBEFORE, TEXTAFTER and TEXTSPLIT gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.