TEXTSPLIT, TEXTBEFORE and TEXTAFTER: Splitting Text Without the Headache

📎 This article includes 1 downloadable practice file ↓

Before Excel 365, splitting “INV/2026/0145” or “Ravi Kumar <[email protected]>” meant nesting LEFT, MID and FIND until nobody could read the formula. Three newer functions make it one short, readable step.

TEXTBEFORE and TEXTAFTER

=TEXTBEFORE(A2, "@")                 ' ravi.kumar@example.in  → ravi.kumar
=TEXTAFTER(A2, "@")                  ' → example.in
=TEXTAFTER(A2, "/", 2)               ' INV/2026/0145 → 0145 (after the 2nd slash)
=TEXTAFTER(A2, "-", -1)              ' last part, counting from the end
=TEXTBEFORE(TEXTAFTER(A2, "<"), ">") ' text between < and >

Handling missing delimiters

If the delimiter isn’t there, both return #N/A. The sixth argument gives a fallback, and the fifth (match_end) treats the end of the text as a delimiter:

=TEXTBEFORE(A2, " ", , , , A2)      ' single-word names stay as they are
=TEXTBEFORE(A2, ",", , , 1)         ' everything if there is no comma

The fourth argument, match_mode 1, makes matching case-insensitive (useful for ” x ” vs ” X “).

TEXTSPLIT: all the pieces

=TEXTSPLIT(A2, "-")                       ' across columns
=TEXTSPLIT(A2, , ", ")                    ' down rows
=TEXTSPLIT(A2, {"-", "/", " "})           ' any of several delimiters
=TEXTSPLIT(A2, ",", , TRUE)               ' ignore empty pieces
=TEXTSPLIT("A=1;B=2;C=3", "=", ";")       ' a 2-D table: columns by "=", rows by ";"
=INDEX(TEXTSPLIT(A2, "-"), 3)             ' just the 3rd piece

A real example: addresses

“12, MG Road, Pune 411001”: =TEXTAFTER(A2, " ", -1) gives the PIN code; =TRIM(INDEX(TEXTSPLIT(A2, ","), 2)) gives the street; =TEXTBEFORE(TEXTAFTER(A2, ",", -1), " ", -1) gives the city.

Older Excel

Without these functions: LEFT(A2, FIND("@", A2) - 1) for text before, MID(A2, FIND("@", A2) + 1, 99) for text after, and the TRIM(MID(SUBSTITUTE(…))) trick for the Nth piece (see split text by delimiter).

Where people go wrong