Split Text with TEXTBEFORE & TEXTAFTER

Excel Formulas › Dynamic Arrays

365TEXTBEFORE

Grab the part of a string before or after a delimiter without counting characters. TEXTBEFORE and TEXTAFTER replace fiddly LEFT/RIGHT/FIND combinations with one clear function.

Quick formula: split an email at the @:
=TEXTBEFORE(A2, "@") // user =TEXTAFTER(A2, "@") // domain
Each returns the text on one side of the delimiter — no FIND or LEN arithmetic needed.

Functions used (tap for the full reference guide):

TEXTBEFORETEXTAFTER

The example

An email split into user and domain.

ABC
1EmailBefore @After @
2ann@dfwexcel.comanndfwexcel.com

The formula

Text on either side of a delimiter:

=TEXTBEFORE(A2, "@") =TEXTAFTER(A2, "@") // ann | dfwexcel.com

How it works

Point at a delimiter and pick a side:

  1. TEXTBEFORE(text, delim) returns everything before the delimiter; TEXTAFTER everything after.
  2. A third argument picks the Nth delimiter — TEXTAFTER(A2, "/", 2) for the second slash.
  3. Use a negative instance number to count from the end: TEXTAFTER(A2, ".", -1) grabs the file extension.
  4. Optional arguments handle “not found” and case sensitivity, so you can default gracefully.

Last segment trick: =TEXTAFTER(A2, "/", -1) returns the part after the last slash — perfect for filenames from a path. Before 365, this took a messy SUBSTITUTE/FIND combination.

Try it: interactive demo

Live demo

Text + delimiter → before / after.

Before · After

Variations

Nth delimiter

Second slash:

=TEXTAFTER(A2, "/", 2)

File extension

After the last dot:

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

If not found

Default value:

=TEXTBEFORE(A2, "@", 1, , , A2)

Pitfalls & errors

365 only. TEXTBEFORE/TEXTAFTER need Excel 365; older versions use LEFT/RIGHT with FIND.

Delimiter not found. By default a missing delimiter errors — supply the if_not_found argument to default instead.

Case sensitivity. Matching is case-insensitive unless you set the match-mode argument.

Practice workbook

📊
Download the free Split Text with TEXTBEFORE & TEXTAFTER practice workbook
TEXTBEFORE/TEXTAFTER examples (formula text + result) with Nth, extension, and default variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get text before or after a character in Excel?
Use =TEXTBEFORE(text, delimiter) and =TEXTAFTER(text, delimiter). For example, splitting an email at "@" gives the user and the domain. Requires Excel 365.
How do I get the text after the last delimiter?
Use a negative instance: =TEXTAFTER(A2, "/", -1) returns everything after the last slash — great for filenames in a path.
What happens if the delimiter isn't found?
By default it returns #N/A. Supply the if_not_found argument to return a default instead.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Split text · Extract email domain · TEXTSPLIT to columns

Function references: TEXTBEFORE · TEXTAFTER