Excel TEXTAFTER Function

Syntax of the TEXTAFTER Function

The TEXTAFTER function takes up to six arguments (inputs inside the parentheses separated by commas). The first two are required; the remaining four are optional:

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

• text: The text string (or cell reference containing a text string) that you want to search within.
• delimiter: The character or substring that marks the boundary. TEXTAFTER returns everything to the right of this delimiter.
• [instance_num]: Optional. Which occurrence of the delimiter to use. Defaults to 1 (the first occurrence). Use a negative number to count from the end of the string, for example, -1 finds the last occurrence.
• [match_mode]: Optional. Controls case sensitivity. Use 0 for a case-sensitive match (the default) or 1 for a case-insensitive match.
• [match_end]: Optional. Determines how Excel handles strings that do not contain the delimiter. Use 0 (default) to return an error when the delimiter is not found, or 1 to return the entire text string instead (treating the beginning of the string as the delimiter).
• [if_not_found]: Optional. The value to return if the delimiter is not found and match_end is 0. By default, Excel returns a #N/A error. Supply any text or value here to display something more user-friendly.

Using TEXTAFTER in Excel

The most common use case for TEXTAFTER is extracting a portion of a text string that comes after a predictable separator. Consider the following spreadsheet where column A contains full email addresses and we want to extract just the domain (everything after the "@" sign) into column B:

To extract the domain in cell B2, you would write:

=TEXTAFTER(A2, "@")

The formula above looks at the text in A2 and returns everything that comes after the first "@" character. For A2, this returns company.com. Dragging this formula down column B processes all remaining rows automatically.

The same pattern works for any separator. To extract the last name from a "First Last" formatted name in cell A2, use a space as the delimiter:

=TEXTAFTER(A2, " ")

For the text "Sarah Connor", this returns Connor; everything after the first space.

Using the instance_num Argument

By default, TEXTAFTER finds the first occurrence of the delimiter. But what if your delimiter appears multiple times and you want the text after the second or third occurrence? That is where the optional instance_num argument comes in. Consider a spreadsheet with hyphenated product codes in column A:

To extract everything after the first dash (color and size) in B2:

=TEXTAFTER(A2, "-")

This returns RED-LARGE. To instead get only the size (everything after the second dash) in C2, set instance_num to 2:

=TEXTAFTER(A2, "-", 2)

With instance_num set to 2, TEXTAFTER finds the second "-" and returns everything to its right, giving you LARGE.

You can also supply a negative instance_num to count from the right. To get everything after the last dash (useful when the number of segments varies across rows), use -1:

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

For "WIDGET-RED-LARGE", this also returns LARGE, since the last dash is the second one. The negative instance_num approach is especially powerful for extracting file extensions or the final segment of any path-like string.

TEXTAFTER: The Easiest Way to Extract the Last Word from Text in Excel

The same pattern works for any separator. To extract the last name from a "First Last" formatted name in cell A2, use a space as the delimiter:

=TEXTAFTER(A2, " ")

For the text "Sarah Connor", this returns Connor, everything after the first space.

If the text contains multiple spaces, you can use the instance_num argument to extract the last word after the final space.

=TEXTAFTER(A2, " ", -1)

For the text "George Washington Carver", this returns Carver; everything after the final space.

Handling the if_not_found Argument

When the delimiter you specify does not appear in the text string, TEXTAFTER returns a #N/A error by default. This can be disruptive when applying the formula across a large dataset where some cells may not contain the delimiter. The optional if_not_found argument lets you specify a clean fallback value instead.

Consider a list of filenames where most include an extension separated by a dot, but some entries are extension-less folder names:

Without the if_not_found argument, the formula in B4 would return a #N/A error because "archive" has no dot. To handle this gracefully, supply a fallback as the sixth argument:

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

Using instance_num of -1 targets the last dot, which correctly handles filenames like "report.final.xlsx" by returning only "xlsx". For row 4, where no dot exists, the formula returns the friendly text "none" instead of an error.

Note that when using if_not_found, you must also supply values for instance_num, match_mode, and match_end, even if you just want the defaults (1, 0, 0), because Excel requires all preceding optional arguments to be present when specifying a later one.

Combining TEXTAFTER with TEXTBEFORE

TEXTAFTER is often used alongside its counterpart, TEXTBEFORE, to split a string into two or more parts. For example, if column A contains full names in "First Last" format and you want to separate them into distinct columns, TEXTBEFORE gets the first name while TEXTAFTER gets the last name:

In cell B2, to extract the first name:

=TEXTBEFORE(A2, " ")

In cell C2, to extract the last name:

=TEXTAFTER(A2, " ")

Together, these two formulas cleanly split "Sarah Connor" into "Sarah" (B2) and "Connor" (C2) without any complex nested logic. You can drag both formulas down their respective columns to process every row at once.

For more complex splits (for instance, extracting the middle segment of a three-part string like "prefix-value-suffix") you can nest TEXTAFTER inside TEXTBEFORE (or vice versa). To isolate just "value" from "prefix-value-suffix":

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

The inner TEXTAFTER first strips "prefix-" to give "value-suffix", and then TEXTBEFORE pulls everything before the remaining dash to return value.

TEXTAFTER vs. the MID and FIND Approach

Before TEXTAFTER was introduced in Microsoft 365, Excel users had to extract text after a delimiter using a combination of the MID function, the FIND function, and the LEN function. For example, to extract the domain from an email address (everything after "@"), the classic approach required:

=MID(A2, FIND("@", A2) + 1, LEN(A2))

This formula uses FIND to locate the position of "@", adds 1 to start just after it, and then uses MID to extract from that position to the end of the string (using LEN as a safely large length). While functional, this approach has several drawbacks compared to TEXTAFTER:

• It is significantly harder to read and understand at a glance, especially for colleagues less familiar with Excel.
• It returns a #VALUE! error when the delimiter is not found, requiring an additional IFERROR wrapper to handle missing delimiters gracefully.
• Targeting a specific instance of a delimiter (e.g., everything after the second comma) requires further nesting with multiple FIND calls, which grows complex quickly.
• Searching from the right side of a string (e.g., text after the last dot) requires a formula using SUBSTITUTE and LEN that is notoriously difficult to construct and audit.
• Case-insensitive matching requires FIND to be replaced by SEARCH, adding yet another function to the chain.

TEXTAFTER handles all of these scenarios directly through its built-in arguments, making formulas dramatically shorter, cleaner, and easier to hand off to a teammate.

Quick Reference: TEXTAFTER Examples

The table below summarizes the most common TEXTAFTER use cases at a glance.

The last example demonstrates match_mode set to 1 (case-insensitive), which means searching for "x" also matches the uppercase "X". The fifth example shows instance_num of -1, which targets the last dot - the right approach for extracting file extensions reliably.

Availability: Which Versions of Excel Support TEXTAFTER?

TEXTAFTER is available in Excel for Microsoft 365 (both Windows and Mac) and Excel for the web. It was introduced as part of the same update that added TEXTBEFORE, TEXTSPLIT, VSTACK, HSTACK, and a suite of other dynamic array functions designed to modernize how Excel handles text and data reshaping.

TEXTAFTER is not available in Excel 2021, Excel 2019, Excel 2016, or any earlier perpetual-license version. If you are working in one of those versions, you will need to use the MID + FIND + LEN workaround described above. If you share workbooks with colleagues who use older Excel versions, their copies will display a #NAME? error wherever a TEXTAFTER formula appears, since the function name is unrecognized in those versions.