Skip to main content

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

Text (required): The original text string you want to extract text from. It can be supplied as:
-- A text string;
Or
-- A cell reference
Delimiter (required): A substring or delimiter that you want to extract the text after it;
Instance_num (optional): The instance of the delimiter in text;
-- The default is 1, which means that when there are multiple delimiters appear in the text string, the text after the first delimiter will be extracted.
-- If you want to search for text starting from the end of the text string, specify a negative number for this parament.
Match_mode (optional): Determines whether the delimiter is case-sensitive;
-- 0 (default): Case sensitive.
-- 1: Case insensitive.
Match_end (optional): Treats the end of text as a delimiter. Default is 0.
-- 0 (default): Don’t match the delimiter against the end of the text.
-- 1: Match the delimiter against the end of the text.
If_not_found (optional): The value to return when no match is found. Default is #N/A error value.

Remarks

1) It is the opposite of the TEXTBEFORE function.
2) When the delimiter is empty, you will get either of the following result:
-- When searching from the left side of the text string (instance_num is positive), the entire text string is returned;
-- When searching from the right side of the text string (instance_num is negative), the empty text is returned;
3) The #VALUE! error occurs if either of the following conditions is meet:
-- Instance_num = 0;
-- Instnce_num is greater than the length of text;
4) The #N/A error occurs if either of the following conditions is meet:
-- Delimiter does not exist in text string;
-- Instance_num is greater than the number of occurrences of delimiter in text string.

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 AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations