TEXTBEFORE and TEXTAFTER in Excel

TEXTBEFORE in Excel returns the text before a delimiter, and TEXTAFTER returns the text after it, with no counting and no FIND. A delimiter is the character that separates parts of the text, like the @ in an email or the / in a file path. This guide is for anyone splitting emails, names or codes, and it covers the optional arguments plus what to use if your Excel doesn't have these functions.

What do TEXTBEFORE and TEXTAFTER do?#

TEXTBEFORE returns everything before a delimiter, and TEXTAFTER returns everything after it. They share the same arguments:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Most of the time you only need the first two. Version note: TEXTBEFORE and TEXTAFTER are available in Microsoft 365, Excel for the web and Excel 2024. Excel 2021 and older show #NAME?.

How to use TEXTBEFORE and TEXTAFTER on an email address#

Give the function the cell and the delimiter. With these emails in column A:

In B2, get the username:

=TEXTBEFORE(A2,"@")

In C2, get the domain:

=TEXTAFTER(A2,"@")

The results are sara.lindqvist and copperfield.io. Fill both down (Windows: Ctrl+D; Mac: ⌘D). The delimiter itself isn't included in either result.

How to use a delimiter with more than one character#

A delimiter can be a whole piece of text, not just one character. Names stored as "Lindqvist, Sara" have a comma and a space between the parts. If you split at the comma only, every first name starts with a space. Split at both:

=TEXTAFTER(E2,", ")

This returns Sara. To flip the name to "First Last", join the two halves with &:

=TEXTAFTER(E2,", ")&" "&TEXTBEFORE(E2,",")

The result is Sara Lindqvist. For more ways to split and rebuild names, see how to split first and last names in Excel.

How to split at the second or last delimiter#

Use instance_num to choose which delimiter to split at. A positive number counts from the start, and a negative number counts from the end. Take these file paths:

The -1 is what makes these work for paths of any depth. Row 4 has four slashes, but =TEXTAFTER(A4,"/",-1) still returns pipeline.csv.

Nesting TEXTBEFORE inside TEXTAFTER#

To get the folder that holds each file, combine the two. First cut off the file name with TEXTBEFORE, then keep only the last folder with TEXTAFTER:

=TEXTAFTER(TEXTBEFORE(A2,"/",-1),"/",-1)

TEXTBEFORE turns the path into Finance/2025/Q3, and TEXTAFTER returns Q3. For row 4 the result is West. The same idea pulls a company out of an email: =PROPER(TEXTBEFORE(TEXTAFTER(A2,"@"),".")) gives Copperfield.

What happens when the delimiter isn't found?#

If the delimiter isn't in the cell, TEXTBEFORE and TEXTAFTER return #N/A. That's useful as a warning, but in a report you may want something friendlier. Use the sixth argument:

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

Or wrap the formula in IFERROR: =IFERROR(TEXTBEFORE(A2,"@"),"No @"). Both show No @ for a cell without an @.

The other two optional arguments help in special cases:

• match_mode 1 ignores capitals. =TEXTAFTER(A2,"id: ",1,1) finds "ID: ", "Id: " and "id: ".
• match_end 1 treats the end of the text as a delimiter. =TEXTBEFORE("Sara"," ",1,0,1) returns Sara instead of #N/A, which helps when some names have no space.

You can also give a list of delimiters in curly brackets: =TEXTBEFORE(A2,{"-","_"}) splits at whichever comes first.

How to do the same in older Excel#

In Excel 2021 and older, use LEFT, MID or RIGHT with FIND. FIND returns the position of the delimiter, and the other functions take the text around it:

The -1 after FIND stops LEFT just before the @, and the +1 starts MID just after it. Splitting at the last delimiter is much harder in older Excel, which is one of the best reasons to use TEXTAFTER where you can. Learn the building blocks in LEFT, RIGHT and MID explained and the LEFT function guide.

If a file must work for colleagues on older versions, use the LEFT and FIND form. Otherwise, TEXTBEFORE is shorter and easier to read.

Common mistakes with TEXTBEFORE and TEXTAFTER#

Most errors come from the delimiter or the version of Excel:

1. #NAME?: your Excel doesn't have these functions. Use the LEFT and FIND versions above.
2. #N/A: the delimiter isn't in the cell. Check for typos, and remember the default match is case-sensitive, so "ID:" doesn't match "id:". Use match_mode 1 to ignore capitals.
3. A stray space in the result: you split at "," when the text has ", ". Include the space in the delimiter, or wrap the result in TRIM.
4. The wrong part: without -1, the split happens at the first delimiter. For the last part of a path or name, use -1.
5. Numbers that won't add up: the result is always text, so "2025" from a code isn't a number. Wrap it in VALUE.

Related functions#

TEXTSPLIT, also in Microsoft 365, splits text at every delimiter and spills the parts into neighbouring cells: =TEXTSPLIT(A2,"/") puts each folder in its own column. Once text is split, you may need to format numbers or dates inside new labels; the TEXT function guide shows how.

Practise it#

Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet. You'll split emails into username and domain, rebuild "Last, First" names, and pull file names and folders out of paths with -1, plus the LEFT and FIND version for older Excel.