Home › Excel functions › TEXTBEFORE & TEXTAFTER

TEXTBEFORE and TEXTAFTER in Excel

TEXTBEFORE returns the text before a delimiter and TEXTAFTER the text after it. They replace the old LEFT/FIND and MID/FIND combinations with one readable function.

Excel CSE · 2 min read
In this guide
  1. Syntax
  2. Examples
  3. First and last names
  4. Missing delimiter
  5. TEXTSPLIT
  6. FAQ

Availability: Microsoft 365, Excel 2024 and Excel for the web. In older versions, use LEFT, MID with FIND.

Syntax

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
ArgumentWhat it means
textThe text or cell to split.
delimiterThe character or text to split at, such as " " or "@".
instance_numWhich occurrence: 1 is the first (default), 2 the second, -1 the last.
match_mode0 = case-sensitive (default), 1 = not case-sensitive.
match_endTreat the end of the text as a delimiter (rarely needed).
if_not_foundWhat to return when the delimiter isn't there. Default is #N/A.

Examples

GoalFormula (A2 = "anna.jansen@example.com")Result
Username=TEXTBEFORE(A2, "@")anna.jansen
Domain=TEXTAFTER(A2, "@")example.com
First name=TEXTBEFORE(A2, ".")anna
Top-level domain=TEXTAFTER(A2, ".", -1)com

First and last names

With "Anna van den Berg" in A2:

  • =TEXTBEFORE(A2, " ") returns Anna (before the first space).
  • =TEXTAFTER(A2, " ") returns van den Berg (after the first space).
  • =TEXTAFTER(A2, " ", -1) returns Berg (after the last space).

When the delimiter is missing

A name without a space returns #N/A. Use the last argument to return something else, such as the whole cell:

=TEXTBEFORE(A2, " ", , , , A2)

Split into several cells: TEXTSPLIT

To split a text into all its parts at once, use TEXTSPLIT: =TEXTSPLIT("P-104-NL", "-") spills P, 104 and NL into three cells. See also Text to Columns and Flash Fill.

Frequently asked questions

Why does TEXTBEFORE return #N/A?

The delimiter isn't in the text. Add a value for if_not_found, or check for extra or missing spaces.

How do I get the last word in a cell?

Use =TEXTAFTER(A2, " ", -1).

Can I use more than one delimiter?

Yes, give an array: =TEXTBEFORE(A2, {"-", "/"}) splits at whichever comes first.

Is TEXTBEFORE available in Excel 2021?

No. It's in Microsoft 365, Excel 2024 and Excel for the web.