When working with complex datasets in Excel, you may often find yourself in a situation when you need to extract data to the right of a certain character or word. This tutorial will show you the fastest way by using the span-new TEXTAFTER function.

## TEXTAFTER function in Excel

The Excel TEXTAFTER function returns text that appears after a given character or substring, which is called the delimiter. In case there are multiple occurrences of the delimiter, you can choose the target occurrence. If the delimiter is not found, the function can return the #N/A error, your own text, or the original string.

The syntax of TEXTAFTER is as follows:

The function has 6 arguments, of which only the first two are required.

**text** (required) - the text to extract from. Can be supplied in the form of a string or cell reference.

**delimiter** (required) - a character or substring after which to do the extraction.

**instance_num** (optional) - the instance of the delimiter after which to extract text. The default value is 1. A negative number starts searching from the end of the original text.

**match_mode** (optional) - defines the delimiter's case-sensitivity. Enabled by default.

- 0 (default) - case-sensitive
- 1 - case-insensitive

**match_end** (optional) - treats the end of *text* as *delimiter*. Disabled by default.

- 0 (default) - match the delimiter exactly as specified in the formula.
- 1 - consider the end of
*text*as*delimiter*. Here's how it practically works: if both*match_end*and*instance_num*are 1, Excel returns an empty string if*delimiter*is not found. If*match_end*is 1 and*instance_num*is -1, the entire string is returned if*delimiter*is not found.

**if_not_found** (optional) - the value to return if the delimiter is not present in the source text. If not set, a #N/A error is returned.

### TEXTAFTER availability

The TEXTAFTER function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

Tips:

- In older Excel versions, you can build your own formula to get text after a certain character.
- If you are looking for the opposite, i.e. extract text
**before**a given character or word, then use the TEXTBEFORE function.

## Basic Excel text after formula

First things first, let's get to know how to build an Excel TEXTAFTER formula in its simplest form using only the required arguments (the first two).

### Excel formula: extract text after character

To extract the text that appears after a specific character, you supply the reference to the cell containing the source text for the first (*text*) argument and the character in double quotes for the second (*delimiter*) argument.

For example, to **extract text after space** the formula is:

`=TEXTAFTER(A2, " ")`

### Excel formula: get text after string

To return the text that occurs after a certain substring, use that substring for the delimiter.

For example, if the last and first names are separated by a comma and a space, use the string ", " for *delimiter*:

`=TEXTAFTER(A2, ", ")`

### Excel formula: extract text after word

In Excel terms, a "word" is nothing else but a "substring". So, to copy text after a specific word into another cell, specify the word as the delimiter.

For instance, to get the text after the word "color", use this formula:

`=TEXTAFTER(A2, "color ")`

Please note that the delimiter includes a space after the word "color " to avoid leading spaces in the results.

Now that you've got a grasp of the basics, let's investigate a few more complex scenarios.

## Return text after Nth occurrence of delimiter

To get text that appears after the nth instance of the delimiter, make use of the *instance_num* parameter.

For example, to extract the text after the second comma, you use the string ", " for *delimiter* and the number 2 for *instance_num*:

`=TEXTAFTER(A2, ", ", 2)`

Again, the delimiter includes a space because, in the original text, the values are separated by a comma and a space.

## Get text after last occurrence of delimiter

To extract the text after the last occurrence of the delimiter, use a **negative value** for the *instance_num* argument.

For example, to return the text after the last comma in A2, set *instance_num* to -1:

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

To extract the text after the last but one comma, set *instance_num* to -2:

`=TEXTAFTER(A2, ", ", -2)`

## Get text after multiple delimiters

To handle multiple delimiters at a time, you can use an array constant like {"x","y","z"} in your TEXTAFTER formula, where *x*, *y,* and *z* are different characters or substrings.

For instance, in the dataset below, the delimiter is either a comma or semicolon, with or without a space. To handle all four variations of the delimiter correctly, we use the array constant {",",", ",";","; "}:

`=TEXTAFTER(A2, {",",", ",";","; "})`

## If delimiter not found, return your own text

If the TEXTAFTER function cannot match delimiter in the original text string, it returns a #N/A error by default. In many situations it makes more sense to output a custom value instead. For this, you need to define the *if_not_found* argument (the last one).

For example, if the word "color " does not exist in cell A2, this formula would throw a #N/A error:

`=TEXTAFTER(A2, "color ")`

To return a blank cell instead, we supply an empty string ("") for *if_not_found*:

`=TEXTAFTER(A2, "color ", , , , "")`

Alternatively, you can type any text or character you want, say a hyphen "-":

`=TEXTAFTER(A2, "color ", , , , "-")`

## If delimiter is not found, return original text

To return the original text every time the TEXTAFTER function cannot match the delimiter, this is what you need to do:

- Set
*instance_num*to -1 to search the source string from right to left. - Set
*match_end*to 1 to treat the end of the string as the delimiter.

When searching backwards, the beginning of the string becomes the "end", so the formula returns the entire text when the delimiter is not found.

In our case, the delimiter is a substring consisting of a colon and a space:

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

If the delimiter is not present in a given cell, the entire cell contents are returned.

## TEXTAFTER formula case-sensitive and case-insensitive

By default, the Excel TEXTAFTER function is case-sensitive, treating lowercase and uppercase delimiters as different ones. To make the formula **case-insensitive**, set the *match_mode* argument to 1 or TRUE.

For example, the below formula only accepts the lowercase "color " as the delimiter:

`=TEXTAFTER(A2, "color ")`

To make the formula case-insensitive, so it accepts the word-delimiter regardless of the letter case, we set the *match_mode* argument to 1:

`=TEXTAFTER(A2, "color ", ,1)`

## Excel TEXTAFTER function not working

If the function does not exist in your Excel, please check out TEXTAFTER availability.

If the function results in an error, it's most likely to be one of these reasons:

A #N/A error occurs when:

- The delimiter is not found the source string. To fix the error, configure the
*if_not_found*argument as explained in this example. - The
*instance_num*value is higher than the total number of the delimiters in*text*.

A #VALUE! error occurs when:

- The
*instance_num*argument is set to 0. - The
*instance_num*value is higher than the total length of*text*.

That's how to use the TEXTAFTER function to extract text after a specific character or word in Excel 365. I thank you for reading and hope to see you on our blog next week!

## Practice workbook for download

TEXTAFTER function – formula examples (.xlsx file)

## 12 comments

Hello, I need help extracting certain numbers from a text, the length of the text always varies, there are numbers before and after the numbers that I need. I need to extract check numbers, but cannot seem to find a formula that works best. The check numbers are always 7 numbers long, would you be able to help with a formula?

182410-Regular Earnings-01/06/2024 0100899 PRO1235

182410-Employer Social Security -01/06/2024 0100899 PRO1235

182653-Phone Allow-01/06/2024 0120859 PRO1252

The check number for employee 182410 would be 010089

The check number for employee 182653 would be 0120859

Hello! To extract the penultimate word from a space-separated text in Excel, we can use a combination of functions.

=INDEX(TEXTSPLIT(A1, " "), COUNTA(TEXTSPLIT(A1, " "))-1)

This formula:

TEXTSPLIT function splits the text in cell A1 into an array of words using the space as the delimiter.

Uses COUNTA function to count the number of words.

Retrieves the penultimate word using INDEX function.

I hope my advice will help you solve your task.

Hi,

I need help with a formula. So far the TEXTAFTER function is the closest, but I also need to return the delimiter.

For example:

Delimiter range (B2:B5) is:

142

539

844

624

Text cell (A1)is:

776499201420108205498514326067

=TEXTAFTER(A1,(B2:B5),,,))

returns: 0108205498514326067

but I need it to return: 1420108205498514326067

Any advice? Thank you

Hi! To show which delimiter you have in the text, use INDEX MATCH and these instructions: How to find substring in Excel. Please check the formula below, it should work for you:

=INDEX(B2:B5,MATCH(TRUE,ISNUMBER(SEARCH(B2:B5,A1)),0)) & TEXTAFTER(A1,(B2:B5),,,)

Thank you for this information. I need help with a function. I'm trying to extract text from a cell that is after 3 characters to the left and ending before a hyphen. The hyphen is not always there. So if the hyphen is not there, can I have it return only the part from 3 from the left to the end and not the entire string? So for example see below. I'm trying to extract the city.

VA Arlington-123 Elm St

AZ Phoenix

CA Los Angeles-345 Maple Rd

Hello! Use the MID function to extract a part of the text string from the text. Use the SEARCH function to determine the number of the hyphen position.

I believe the following formula will help you solve your task:

=IFERROR(MID(A1,4,SEARCH("-",A1,4)-4),MID(A1,4,30))

Hello Alexander,

I was hoping you could help me out. I am trying to break a sequence string into parts by displaying each part in its own cell. Here is the sequence: mX*mX*mX*|rArUrArUrKrCrArArCrArCrUrUrCrUrUrArGrArErCrUrArGrArArArUrArGrCrArArGrUrUrArArArArUrArArGrG|rCrUrArGrUrCrCrGrUrUrArUrCrArArCrUrNrGrArArArArVrGrUrGrGrCrArCrCrGrArGrQrCrGrGrU|rGrCrX*mX*mX*mX

I want my function to search for the | symbols and display each chunk. I want the first cell to display the part furthest to the right "rGrCrX*mX*mX*mX". Second cell this part: "rCrUrArGrUrCrCrGrUrUrArUrCrArArCrUrNrGrArArArArVrGrUrGrGrCrArCrCrGrArGrQrCrGrGrU" and for the 3rd and 4th also. Can I use this function for this?

Thanks

Luke

Hello!

I think that your problem cannot be solved with the standard Excel tools. You can split the text into cells using the recommendations of this article: How to split cells in Excel: Text to Columns, Flash Fill and formulas.

However, you can quickly solve your problem with our Ultimate Suite for Excel. It contains the Split Text tool to divide text by cells using delimiters or a template. The Flip Data tool will help you to reverse the column order in one click.

It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Hi,

How would I extract text after a number? and how about after the second number? Let's say there are words and numbers in a cell - think an address, but on steroids where text to column doesn't work (opens to about 15 columns).

Thank you,

Elena

Hello!

If the numbers and text are separated by a space or some other delimiter, you can try the Split Text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Hi, I'm new to excel and wanted to find just one character in a string of characters without destroying the string. Hopefully without programming a script.

Example: "thebrownfoxjumpsoveralazydog"

If I wanted to find just the single characters after the letter character "o"

example 1: instance 1: after first "o" = w

example 2: instance 2: after second "o" = x

example 3: instance 3: after third "o" = v

If you can help with a solution it would be much appreciated.

Regards,

Rick

Hello!

To extract characters, use the TEXTAFTER function and the recommendations from the article above. Merge characters using the TEXTJOIN function.

=TEXTJOIN(",",TRUE,IFERROR(LEFT(TEXTAFTER(A1,"o",ROW(B1:B5)),1),""))

You can also use regular expressions to extract all the characters you want from the text. Look for the example formulas here: How to extract substrings in Excel using regular expressions (Regex).