Excel - TEXTAFTER Function
TEXTAFTER Function
The Excel TEXTAFTER function retrieves the text after matching the delimiter/character/text either from the front or rear side of the text string. The TEXTAFTER function is not available in the older version of Microsoft Excel and is contrary to the TEXTBEFORE function. By default, this function is case-sensitive. You can set the match_mode argument to 1 for case insensitive. For example, you can extract the extensions of multiple image files on the laptop.
Compatibility
This advanced Excel function is compatible with the following versions of MS-Excel −
- Excel for Microsoft 365
- Excel for Microsoft 365 for Mac
- Excel for the web
Syntax
The syntax of the TEXTAFTER function is as follows −
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Arguments
You can use the following arguments with the TEXTAFTER function −
| Argument | Description | Required / Optional |
|---|---|---|
| Text | It specifies the text string to fetch the specific text. | Required |
| Delimiter | A specific text that acts as a point is presented in the string. After that point, you can retrieve the text. | Required |
| instance_num | It indicates the delimiters instance to retrieve the resulting text. By default, its value is 1. If its value is negative, text searching is from the rear. | Optional |
| match_mode | This argument contains a binary value of 0 or 1. By default, 0 is used for case sensitive. Otherwise, write 1 for case insensitive. | Optional |
| match_end | Its value can be 0 or 1, 0 for no match to end and 1 for match to end. By default, 0 is used. It considers the texts rear as a delimiter. | Optional |
| If_not_found | The specific text message will be retrieved if no text match is found. | Optional |
Points to Remember
- If the delimiter is not matched in the text string, the TEXTAFTER function will retrieve the #N/A error.
- If the instance_num is larger than the text string or equivalent to zero, then the TEXTAFTER function will retrieve the #VALUE! error.
- The TEXTAFTER function will display a null value if the cell references empty string values.
Examples of TEXTAFTER Function
Lets elaborate with a few exciting examples of the TEXTAFTER function.
Example 1
The TEXTAFTER function in Excel is a text manipulation function that allows you to extract the portion of a string that appears after a specified delimiter.
Solution
Step 1 − Assume the sample dataset consists of two columns named Product ID and Serial Number.
Step 2 − Write the formula =TEXTAFTER(A3,"-") in the B3 cell and press the Enter.
The TEXTAFTER function will return the only serial number just after the -delimiter.
Step 3 − Similarly, you can retrieve the serial numbers of the other remaining cells by dragging the + sign at the bottom right corner of the B3 cell to the B12 cell.
Example 2: Using Multiple Delimiters
Lets say the text string contains various delimiters. Then, in this case, you can use the array and specify all delimiters separated by the commas in the second argument. Enter the formula =TEXTAFTER(B3,{"'","'","-"}) in the B5 cell and press the Enter tab.
Therefore, the TEXTAFTER function will extract the text after the first delimiter in the given string.
In another scenario, if you wish to obtain the text after the last delimiter, you can set the instance_num to -1. Write the formula =TEXTAFTER(B3,{"'","'","-"},-1) in the B7cell and hit the Enter button.
Example 3
If the delimiter is not matched in the text string, use the TEXTAFTER function to write your message.
Solution
The TEXTAFTER function retrieves the #N/A error if the delimiter is unavailable in the text string. In the below screenshot, the colon : delimiter is not present in the A2 cell.
Now, modify the formula =TEXTAFTER(A2,":",,,,"Welcome to 93.68 channel") in the B2 cell and hit the Enter.
Therefore, the TEXTAFTER function will retrieve the message Welcome to 93.87 FM channel you wrote in the sixth argument.
Example 4
Set the match_mode to 1 for case insensitivity.
Solution
The TEXTAFTER function distinguishes uppercase and lowercase differently. Suppose you provide text in lowercase in the second argument, but the text is uppercase in the input string. This function retrieves the #N/A error because the exact match is not identified in the TEXTAFTER function. Write the formula =TEXTAFTER(B2,"L") in the B4 cell.
Once you hit Enter, the TEXTAFTER function will return the #N/A error.
To ignore the case insensitivity, you can set the match_mode to 1. Write the formula =TEXTAFTER(B2,"L",,1) in the B4 cell and press the Enter tab.
Therefore, the TEXTAFTER function will retrieve the text come to Disney land, where the case insensitivity has been ignored.
Download Practice Sheet
You can download and use the sample data sheet to practice the TEXTAFTER function.