Skip to content

Excel function · Text

Excel TEXTAFTER function

TEXTAFTER returns the part of a text that comes after the delimiter you give it: the domain of an email address, a file extension, the suffix of a product code. It is available in Microsoft 365 and Excel 2024.

In Turkish Excel: SONRAKİMETİN

What it does

TEXTAFTER replaces the formula you would otherwise build from RIGHT, LEN and FIND: it looks for the delimiter and returns everything after it. =TEXTAFTER("sales@northgate.com","@") returns "northgate.com".

Its handiest feature is finding the last delimiter directly: with -1 as the third argument it counts from the end. That gets the extension from a file name with several dots, such as "report.oct.xlsx", or the suffix from "KBL-1525-SY", in one formula. If the delimiter is missing the result is #N/A, and the last argument lets you return something else instead.

TEXTAFTER exists only in Microsoft 365, Excel 2024 and Excel for the web. For the part before the delimiter use TEXTBEFORE, and to split the text at every delimiter use TEXTSPLIT.

Syntax

English Excel=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Turkish Excel=SONRAKİMETİN(metin; sınırlayıcı; [kaçıncı]; [eşleştirme_modu]; [eşleştirme_sonu]; [bulunamazsa])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
textRequiredThe text, or cell, to search.
delimiterRequiredThe delimiter after which the text is returned; one or more characters.
instance_numOptionalWhich occurrence to use; 1 if omitted. Negative numbers count from the end: -1 is the last delimiter. It cannot be 0.
match_modeOptional0 (default): case-sensitive. 1: case is ignored.
match_endOptional0 (default): the end of the text is not a delimiter. 1: the end counts as one, so the result is empty text when the delimiter is missing.
if_not_foundOptionalThe value to return when the delimiter is not found. If omitted, the result is #N/A.

Examples

1Take the text after the first and the last hyphen

Column B returns everything after the first hyphen; column C uses -1 to take only the colour suffix after the last one.

B2fx=TEXTAFTER(A2,"-")
Take the text after the first and the last hyphen: example table
ABC
1Product CodeCode without GroupColour Suffix
2KBL-1525-SY1525-SYSY
3PRZ-21-BY21-BYBY
4SGT-0016-C0016-CC
5KNL-120-GR120-GRGR
  • B2 (filled down to B5)=TEXTAFTER(A2,"-")
  • C2 (filled down to C5)=TEXTAFTER(A2,"-",-1)

The first row returns 1525-SY and SY; the third returns 0016-C and C.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Get the extension from a file name

Some file names contain several dots. -1 picks the last dot, so every row returns only the extension.

B2fx=TEXTAFTER(A2,".",-1)
Get the extension from a file name: example table
AB
1File NameExtension
2report.oct.xlsxxlsx
3invoice_INV-2025-0042.pdfpdf
4quote.v2.docxdocx
5stock.csvcsv
  • B2 (filled down to B5)=TEXTAFTER(A2,".",-1)

B2 shows xlsx and B4 shows docx. Without the third argument, the first row would return "oct.xlsx".

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Return a default when no colour is given

The colour follows " - "; products without a colour come in the standard colour. The last argument returns "Standard" on rows without the delimiter.

B2fx=TEXTAFTER(A2," - ",1,0,0,"Standard")
Return a default when no colour is given: example table
AB
1DescriptionColour
2Grounded socket - WhiteWhite
3Circuit breakerStandard
4Cable duct - GreyGrey
5Light switch - AnthraciteAnthracite
  • B2 (filled down to B5)=TEXTAFTER(A2," - ",1,0,0,"Standard")

B2 shows White, and B3, which has no colour, shows Standard.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The delimiter is not in the text, or it does not appear as many times as requested.

Fix: Give the last argument a value: =TEXTAFTER(A2," - ",1,0,0,"Standard").

#VALUE!

Cause: instance_num is 0 or larger than the length of the text.

Fix: Use 1, 2… or -1, -2… to count from the end.

#NAME?

Cause: The file was opened in an older version of Excel that does not know TEXTAFTER.

Fix: In older versions, =RIGHT(A2,LEN(A2)-FIND("@",A2)) does the same job.

Good to know

  • To get the last word, use a space as the delimiter and -1 as instance_num: =TEXTAFTER(A2," ",-1).
  • The result is text; to calculate with the digits after the delimiter, convert it with --.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Put the colour suffix after the last hyphen of each product code into a new Colour column

Convert to table

Bring your product list into the tool and type this. Nexell builds the Colour column with a real Excel text formula and shows it in the preview first; the formula stays live in the downloaded file.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I get the text after the last dot in Excel?

In Microsoft 365, use =TEXTAFTER(A2,".",-1). For "report.oct.xlsx" the result is "xlsx"; the other dots in the name are ignored.

What is the difference between TEXTAFTER and RIGHT?

RIGHT needs to know how many characters to take from the end. TEXTAFTER looks for the delimiter instead, so no counting is needed, and you can set a value for when it is not found.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free