Excel TEXTAFTER function
The TEXTAFTER function extracts and returns the text that occurs after a given substring or delimiter.
Note: This function is only available in Excel for Microsoft 365 on the Insider channel.
Syntax
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments
Remarks
Return value
It returns the extracted text string.
Example
Here, we take the following text list as an example to demonstrate how to use the TEXTAFTER function to extract text under different conditions.
#Example1: Extract the text after the first delimiter in Excel
To extract the text that occurs after the first comma in the specified cells, you can apply the TEXTAFTER function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTAFTER(A2,",")
Note: In this case, only the first two arguments are required in the formula.
#Example2: Extract the text after the Nth delimiter in Excel
By default, the TEXTAFTER function extracts the text after the first delimiter in the text string, even if there are multiple delimiters in the text. If you want to extract the text after the Nth delimiter, such as the second comma in this case, you can apply the TEXTAFTER function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.
=TEXTAFTER(A2,",",2)
Note: To extract the text that occurs after the nth delimiter, you just need to specify the instance_num argument as the corresponding number. In this case, I enter the number 2 to indicate the second comma.
#Example3: Extract the text after a given substring (case insensitive) in Excel
By default, the TEXTAFTER function is case sensitive. If you want to disable the case sensitivity, please apply the TEXTAFTER function as follows.
Select a cell (say B2 in this case), enter the following formula and press the Enter key to get the result. Select this result cell and drag its AutoFill Handle down to get the rest of the results.
=TEXTAFTER(A2,"Consultant",1,1)
Note: To disable the case sensitivity in the TEXTAFTER function, you need to specify the Match_mode argument as 1. In this case, all the text that occurs after the substring “Consultant” or “CONSULTANT” will be extracted.
#Example4: Return a custom value when TEXTAFTER does not match any text
As shown in the screenshot above, by default, the TEXTAFTER function returns #N/A when no matches are found. To return a custom value rather than errors, you can apply the TEXTAFTER function as follows.
Select a cell next to the first text cell where you want to extract the text from, enter the following formula and press the Enter key to get the result. Select this result cell and drag its AutoFill Handle down to get the rest of the results.
=TEXTAFTER(A2,"Consultant",1,1,,"text not found")
Note: In this formula, “text not found” is the custom value to return when TEXTAFTER does not match any text. You can change it as you need.
#Example5: Extract the text after the last delimiter in Excel
Usually, you can specify the instance_num parameter to extract the text after the nth delimiter in a text string. If you need to extract the text after the last delimiter, the easiest way is to specify a negative number for this parameter.
For example, to extract the text that occurs after the last comma in the table below, the TEXTAFTER function can be changed as follows.
Select a cell (say B2 in this case), enter the following formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the rest of the results.
=TEXTAFTER(A2,",",-1)
Note: Here -1 in the formula helps to search the original text string from the end.
Related Functions
Excel TEXTBEFORE function
The TEXTBEFORE function returns the text that occurs before a given substring or delimiter.
Excel TEXTSPLIT Function
The TEXTSPLIT function splits text strings by a given delimiter to an array across columns and rows.
Excel VALUETOTEXT Function
The VALUETOTEXT function converts any specified value to text.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
