Table of Contents
The TEXTAFTER function in Excel is a useful tool that allows users to extract a specific number of characters from a given text string, starting after a specified character or sequence of characters. This function can be used to obtain specific information from a larger text string, such as names, dates, or keywords. To use the TEXTAFTER function, simply input the starting character or sequence, followed by the number of characters to extract, and the text string from which the information is to be extracted. This function can save time and effort when working with large amounts of text data in Excel.
Returns text that occurs after given character or string. It is the opposite of the TEXTBEFORE function.
Syntax
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
The TEXTAFTER function syntax has the following arguments:
text The text you are searching within. Wildcard characters not allowed. Required.
delimiter The text that marks the point after which you want to extract. Required.
instance_num The instance of the delimiter after which you want to extract the text. By default, instance_num = 1. A negative number starts searching text from the end. Optional.
match_mode Determines whether the text search is case-sensitive. The default is case-sensitive. Optional. Enter one of the following:
0 Case sensitive.
1 Case insensitive.
match_end Treats the end of text as a delimiter. By default, the text is an exact match. Optional. Enter one of the following:
0 Don’t match the delimiter against the end of the text.
1 Match the delimiter against the end of the text.
if_not_found Value returned if no match is found. By default, #N/A is returned. Optional.
Remarks
When searching with an empty delimiter value, TEXTAFTER matches immediately. It returns the entire text when searching from the front (if instance_num is positive) and empty text when searching from the end (if instance_num is negative).
Example | Result |
|---|---|
=TEXTAFTER(“Red riding hood’s, red hood”, “hood”) | ’s, red hood |
=TEXTAFTER(“Red riding hood’s, red hood”, “”) | Red riding hood’s, red hood |
=TEXTAFTER(“Red riding hood’s, red hood”, “”, -1) |
Errors
Excel returns a #N/A error if delimiter isn’t contained in text.
Excel returns a #VALUE! error if instance_num = 0 or if instance_num is greater than the length of text.
Excel returns a #N/A error if instance_num is greater than the number of occurrences of delimiter.
Data | |
Little Red Riding Hood’s red hood | |
Little red Riding Hood’s red hood | |
Formulas | Results |
=TEXTAFTER(A2,”Red”) | Riding Hood’s red hood |
=TEXTAFTER(A2,”basket”) | #N/A |
=TEXTAFTER(A3,”red”,2) | hood |
=TEXTAFTER(A3,”red”,-2) | Riding Hood’s red hood |
=TEXTAFTER(A3,”Red”,,FALSE) | #N/A |
=TEXTAFTER(A2,”red”,3) | #N/A |
Data | |
Marcus Aurelius | |
Socrates | |
Immanuel Kant | |
Formulas | Results |
=TEXTAFTER(A2,” “,,,1) | Aurelius |
=TEXTAFTER(A3,” “,,,0) | #N/A |
=TEXTAFTER(A3,” “,,,1) | |
=IF(TEXTAFTER(A3,” “,,,1)=””,A3) | Socrates |
=TEXTAFTER(A4,” “,,,1) | Kant |
See Also
Cite this article
stats writer (2024). How can I use the function TEXTAFTER in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-function-textafter-in-excel/
stats writer. "How can I use the function TEXTAFTER in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-function-textafter-in-excel/.
stats writer. "How can I use the function TEXTAFTER in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-function-textafter-in-excel/.
stats writer (2024) 'How can I use the function TEXTAFTER in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-function-textafter-in-excel/.
[1] stats writer, "How can I use the function TEXTAFTER in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the function TEXTAFTER in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.