Excel TEXTAFTER function

Summary

The Excel TEXTAFTER function extracts text occurring after a specified delimiter within a string. It’s useful for parsing strings and retrieving specific segments of text based on a delimiter. This function is the counterpart to the TEXTBEFORE function.
Syntax
				
					=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
				
			
  • text: The text string to search within. Required.
  • delimiter: The text marking the point after which to extract. Required.
  • instance_num: [Optional] The instance of the delimiter to use. Default is 1. Negative values start from the end.
  • match_mode: [Optional] 0 for case-sensitive, 1 for case-insensitive. Default is case-sensitive.
  • match_end: [Optional] 0 to not match the end of text, 1 to treat end as a delimiter.
  • if_not_found: [Optional] Value returned if no match is found. Default is #N/A.
Return value
The portion of the text that occurs after the specified delimiter.

How to use

TEXTAFTER is versatile for extracting segments from text strings, especially when dealing with structured text formats or delimiters. It’s often used in data parsing, cleaning, and preparation.

Examples

Simple TEXTAFTER
Extracting Text After a Specific Word: To get the text after a word in a string:
				
					=TEXTAFTER(A1, "word")
				
			
If A1 is “Find the word here”, it returns ” here”.
TEXTAFTER with Instance Number
Extracting After the Second Occurrence: Using TEXTAFTER to target the second instance of a delimiter:
				
					=TEXTAFTER(B2, ",", 2)
				
			
Extracts text after the second comma in B2.
TEXTAFTER in Data Parsing
Parsing Email Domains: Extracting the domain from an email address:
				
					=TEXTAFTER(C2, "@")
				
			
Extracts text after “sample”, “Sample”, or any case variation in D2.
TEXTAFTER with End Matching
Considering End of Text as Delimiter: Treating the end of the text as a delimiter:
				
					=TEXTAFTER(E2, "end", , , 1)
				
			
Extracts text after “end” in E2, including if “end” is at the text’s end.
TEXTAFTER with Custom 'Not Found' Response
Custom Response for No Match: Specifying a custom return value when no match is found:
				
					=TEXTAFTER(F2, "missing", , , , "Not found")
				
			
Returns “Not found” if “missing” is not in F2.

Additional Notes

  • Ideal for extracting specific portions from strings where the delimiter’s position is known or consistent.
  • Can be combined with other text functions for complex string manipulations.
  • Useful in scenarios like data import, where specific information needs to be isolated from a larger text string.

Related Functions

Excel TEXTJOIN function

The Excel TEXTJOIN function combines text from multiple ranges, including a delimiter, perfect for creating comprehensive strings.

Excel CONCAT function

The Excel CONCAT function merges multiple text strings into one, perfect for data concatenation without using the ampersand (&).

Excel TEXTSPLIT function

The Excel TEXTSPLIT function divides text into multiple cells based on delimiters, simplifying data organization and analysis.

Excel TEXTBEFORE function

The Excel TEXTBEFORE function extracts text before a specified delimiter, useful for segmenting and analyzing string data.

Content Navigation