Formuladrill

Text Updated November 2026

TEXTBEFORE and TEXTAFTER in Excel

TEXTBEFORE(text, delimiter, [instance_num]) returns everything before a delimiter and TEXTAFTER(text, delimiter, [instance_num]) returns everything after it. A negative instance number counts from the end, so -1 means the last occurrence. They replace LEFT, RIGHT and MID combined with FIND.

Syntax

TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
PartWhat it is
textThe text to take a piece from.
delimiterThe character or text that marks the split, such as a space, @ or a comma.
instance_numWhich occurrence to split at. 1 is the first (default), -1 is the last.
match_mode0 case-sensitive (default), 1 ignores case.
match_end1 treats the end of the text as a delimiter. Default 0.
if_not_foundWhat to return when the delimiter is not in the text. Default is #N/A.

Examples

=TEXTAFTER("ana@example.com", "@")

Result: example.com

=TEXTBEFORE("Lopez, Maria", ",")

Result: Lopez

=TEXTAFTER("report.final.xlsx", ".", -1)

Result: xlsx

-1 splits at the last dot, which is how to get a file extension.

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

Result: The first word, or the whole text when there is no space

The sixth argument avoids #N/A for one-word cells.

Common mistakes

  • #N/A means the delimiter is not in the text. Give the sixth argument a fallback.
  • #NAME? means the Excel version does not have these functions. They are in Microsoft 365 and Excel 2024, not Excel 2021 or 2019. Use LEFT, MID and FIND there.
  • The first dot is used when the last one was wanted. Pass -1 as the instance number.
  • A comma followed by a space leaves a leading space in the result. Use ", " as the delimiter or wrap the result in TRIM.

Practice TEXTBEFORE and TEXTAFTER

These drills check your answer on live data, free, no account needed.

  • File extensionReturn the file extension: the text after the last dot in each file name. Some names contain more than one dot. Fill B2 and B3.
  • Split last name, first nameEach name is written as the last name, a comma, a space, then the first name. Put the last name in column B and the first name in column C for both rows. The names change, so find the comma rather than counting characters.
  • Pull out the domainExtract everything after the @ sign from each email address. Fill B2 and B3.

Questions

How do I get the text after the last occurrence of a character?

=TEXTAFTER(A2, "/", -1) returns everything after the last slash. A negative instance number counts from the end.

What can I use instead of TEXTBEFORE in older Excel?

=LEFT(A2, FIND(" ", A2) - 1) returns the text before the first space. For the text after it, =MID(A2, FIND(" ", A2) + 1, LEN(A2)).

Are TEXTBEFORE and TEXTAFTER case-sensitive?

Yes by default. Pass 1 as the fourth argument to ignore case in the delimiter.

Related

  • LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
  • SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
  • TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
  • TEXTJOINHow the Excel TEXTJOIN function joins a range into one string with a separator and skips empty cells, with examples for comma lists and a conditional join.

Previous: LETNext: WEEKDAY