by Alexander Frolov, updated on
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.
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.
match_end (optional) - treats the end of text as delimiter. Disabled by default.
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.
The TEXTAFTER function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
Tips:
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).
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, " ")
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, ", ")
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.
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.
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)
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 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 ", , , , "-")
To return the original text every time the TEXTAFTER function cannot match the delimiter, this is what you need to do:
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.
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)
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:
A #VALUE! error occurs when:
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!
TEXTAFTER function – formula examples (.xlsx file)
Table of contents