How To Use Excel TEXTAFTER Function – Extract Text Easy Way

In working with data, we may need to extract data to the right of a symbol or word. The Len function and Text to Columns are two well-known options. However, you cannot specify delimiters with the Len function. By using Text to Columns, data can be divided based on the delimiter you specify. In this article let us look at how to use the TEXTAFTER function in Excel to overcome these limitations.

What is TEXTAFTER function in Excel

TEXTAFTER function in excel is a new addition to the functions list. This is currently available in Excel365 version. This function is more advanced and offers additional flexibility to extract text.

TextAfter function in excel extracts and returns the text that comes after a given delimiter. Delimiters can be symbol, character or words. This function offers flexibility in terms of choosing the occurrence of the delimiter. Return your own text in case of an error.

Syntax of TEXTAFTER function in Excel

The Syntax of TextAfter function in excel is as follows

=TEXTAFTER(Text, Delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Arguments of TextAfter function

TextAfter function has 6 arguments. 2 are mandatory and the rest are optional.

Text : The Text to extract data from. This can be either a string or a cell reference. This is a Mandatory/ required argument.

Delimiter : This is a symbol or character or string after which you want to extract. This is also a Mandatory argument.

Instance_Num : The instance of the delimiter after which you want to extract the text.  By default, instance_num = 1.  A negative number starts searching text from the end. This is an Optional Argument.

Match_Mode : This is an Optional Argument.. Determines whether the text search is case-sensitive. This argument takes 2 values.

• 0 – Case Sensitive.
• 1 – Case Insensitive.

The default is case-sensitive.

Match_End : Optional Argument. Treats the end of text as a delimiter. By default, the text is an exact match. This argument takes also takes 2 values.

• 0 – Don’t match the delimiter against the end of the text.
• 1 – Match the delimiter against the end of the text.

If_Not_Found : The value to return if no match is found. By default, #N/A is returned. This is also an Optional argument.

How to Extract Text using TEXTAFTER function in Excel

Extract Text – Single Delimiter

In the below example we use “,” as the delimiter.

We need to extract the data after “,” from Column A into Column B. The formula to use in Column B is =TextAfter(A2,”,”). Since the delimiter is “,” textafter function returns the text or data after “,“. See the below image to understand.

Note : While we use “,” in the example, you can replace it with any character (like ; / ] \ etc.) or an alphabet or a word.

Extract Text – Single Delimiter – After a Specific Instance

In the below image we have a single Delimiter which is “,“. However the difference is we have the delimiter repeating multiple times. We need to extract the text after the 2nd appearance of “,“. To Do this we need to use the Instance_Num argument.

The formula to use in Column B is =TextAfter(A2,”,”,2). Since the delimiter is “,” and the instance_num argument as 2. TextAfter function in excel returns text after the 2nd “,“.

Extract Text – Multiple Delimiters

Instead of a single Delimiter, you can also use multiple delimiters and extract the text from given data. To use multiple delimiters you have to use them in the form of an array. Something like {“d1″,”d2″,”d3”} where d1,d2 and d3 are delimiters.

Let us understand with an example. See the below image. Data is having “,” (Comma), “;“(Semi-Colon), “[ “(Open Bracket) and ” ” (Space) as delimiters. We need the data separated by each delimiter. The formula in this case will be =TextAfter(A2,{“,”,”[“,“;”,” “})

Extract Case Sensitive data using TextAfter function in excel

By default, TextAfter function in excel is case sensitive. This means it treats lowercase and uppercase delimiters as different ones. To use the case sensitive feature you need to use the Match_Mode Argument.

See the below image. The result in column B is the default option which is case-sensitive. The result in Column C is using the Case-Insensitive option.

Note the usage of 1 in the match mode argument. This tells that the delimiter should be the same irrespective of whether it is upper case or lower case.

Extract Text from end or backwards

In case if you want to extract data from backwards TextAfter function in excel can be handy.

In this situation use the Instance_Num argument. The only difference is instead of a positive number you should use a negative number. See the below image to understand this.

When you use a a negative number in the instance_number argument, the TextAfter function in excel returns data after the last occurrence of the delimiter.

See the below image to understand this better.

Return a Value or Text if Delimiter is not found

TextAfter function in excel returns a #N/A error by default, in case delimiter is not found. However, it is good to have a value instead of an error. To have this enabled, you need to use the if_not_found argument.

The if_not_found argument helps the excel textafter function to return a value instead of an error. The below example explains how to use this option.

In the below data the delimiter “[” do not exist. In this case the function returns an error. To avoid this we should use a ” ” (space) or a text like “Delimiter not Found”. So the textafter function will be as follows.

=TEXTAFTER(A6,”[“,,,,”Delimiter Not Found”)

Return the original value if Delimiter is not found

Do you need to return the original text every time the TEXTAFTER function cannot find the delimiter? You have the flexibility to do so.

Two basic things to note here are

• Match_End Argument should be 1
• Instance_Num argument should be -1

TextAfter function will be as follows.

=TextAfter(A4,”:”,-1,,1)

Since the match_end argument is set to 1, the function starts counting from backwards to start. The instance_num is set to -1 so TextAfter will return the entire string if delimiter is not found.

In the same formula if the instance_num is set to 1 and delimiter is not found. The return value will be blank ” “.

TEXTAFTER function in Excel – Points to remember

• Excel TextAfter function returns a #N/A Error if
  • The Delimiter is not available.
  • The Instance_Number is higher than the number of delimiters in the text.
• The TextAfter function is Case Sensitive by default.
• TextAfter function in excel returns a #Value error if
  • Instance_Number is set to 0.
  • Text is empty

Conclusion

This article explains and focuses on the basic uses of TEXTAFTER function in excel. However, there are other advanced ways you can use TEXTAFTER function(with combination of various other functions.)

What Do you think of the TEXTAFTER function. Do You use it . Share in the comments.

Do Not Forget to Share this article with your Friends !!!

TextAfter on Microsoft