How to use the TEXTAFTER function
The TEXTAFTER function extracts a string after a specific substring in a given value.
Formula in cell E3:
The TEXTAFTER function is available to Excel 365 users.
Table of Contents
1. TEXTAFTER Function Syntax
TEXTAFTER(input_text,text_after, [n], [ignore_case])
2. TEXTAFTER Function Arguments
| input_text | Required. The original string. |
| text_after | Required. The string to search for, text after this string is extracted. |
| [n] | Optional. The instance of text_after string, default is 1. |
| [ignore_case] | Optional. FALSE represents case sensitive search, default is TRUE. |
3. TEXTAFTER Function example
The formula in cell E4 extracts text from cell B4 after given string in cell C3.
Formula in cell E4:
Explaining formula
Step 1 - TEXTAFTER function
TEXTAFTER(input_text,text_after, [n], [ignore_case])
Step 2 - Populate arguments
input_text - B4
text_after - C4
[n] - Optional, default is 1.
[ignore_case] - Optional, default is TRUE meaning not case sensitive
Step 3 - Evaluate function
TEXTAFTER(B4, C4)
becomes
TEXTAFTER("A dolphin is also breathing air.", "is")
and returns " also breathing air.".
4. TEXTAFTER Function - case sensitive example
This example shows how to use the TEXTAFTER function also considering upper and lower letters.
Formula in cell E4:
Explaining formula
Step 1 - TEXTAFTER function
TEXTAFTER(input_text,text_after, [n], [ignore_case])
Step 2 - Populate arguments
input_text - B4
text_after - C4
[n] - Optional, default is 1.
[ignore_case] - FALSE meaning case sensitive.
Step 3 - Evaluate function
TEXTAFTER(B4, C4,,FALSE)
becomes
TEXTAFTER("A blue whale breathes Blue air.", "Blue", , FALSE)
and returns " air." in cell E3.
5. TEXTAFTER Function alternative (earlier Excel versions)
The TEXTAFTER function lets you perform both a regular and a case-sensitive search based on the fourth argument [ignore_case]. The SEARCH and FINDÂ functions are used in the alternative formulas below.
5.1 TEXTAFTER Function alternative
This formula performs the same thing as the TEXTAFTER function, it works in all Excel versions. The formula extracts text after a given substring.
Formula in cell E4:
Explaining formula
Step 1 - Find character position of substring
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is NOT a case-sensitive search.
SEARCH(find_text,within_text, [start_num])
SEARCH(C3, B3)+1
becomes
SEARCH("breathes", "A blue whale breathes air.")+1
becomes
14+1 equals 15.
Step 2 - Count characters in cell B3
The LEN function returns a number representing the number of characters for a given string.
LEN(text)
LEN(B3)
becomes
LEN("A blue whale breathes air.")
and returns 26.
Step 3 - Count characters in cell C3
LEN(C3)
becomes
LEN("breathes")
and returns 8.
Step 4 - Extract text after based on position
The RIGHT function extracts a specific number of characters always starting from the right of the text string.
RIGHT(text, [num_chars])
RIGHT(B3, LEN(B3)-SEARCH(C3, B3)-LEN(C3))
becomes
RIGHT("A blue whale breathes air.", 26-14-8+1)
becomes
RIGHT("A blue whale breathes air.", 5)
and returns " air.".
5.2 TEXTAFTER Function alternative - case sensitive
This formula performs a case-sensitive search in order to extract text after the given substring.
Formula in cell E3:
Explaining formula
Step 1 - Find character position of substring
The FIND function returns a number representing the position of character at which a specific text string is found reading left to right. This function performs a case-sensitive search.
FIND(find_text,within_text, [start_num])
FIND(C3, B3)+1
becomes
FIND("a", "A blue whale breathes air.")+1
becomes
10+1 equals 11.
Step 2 - Count characters in cell B3
The LEN function returns a number representing the number of characters for a given string.
LEN(text)
LEN(B3)
becomes
LEN("A blue whale breathes air.")
and returns 26.
Step 3 - Count characters in cell C3
LEN(C3)
becomes
LEN("a")
and returns 1.
Step 4 - Extract text after based on character position
The RIGHT function extracts a specific number of characters always starting from the right of the text string.
RIGHT(text, [num_chars])
RIGHT(B3,LEN(B3)-FIND(C3,B3)-LEN(C3)+1)
becomes
RIGHT("A blue whale breathes air.",26-11-8+1)
becomes
RIGHT("A blue whale breathes air.",16)
and returns "le breathes air.".
6 Extract string between two substrings
The formula in cell F3 extracts a string between two given substrings, they are in cells D3 and D6. The original string is in cell B3.
Formula in cell F3:
Explaining formula
Step 1 - Extract text after given string
TEXTAFTER(B3, D3)
becomes
TEXTAFTER("A blue whale breathes air.", "blue")
and returns " whale breathes air.".
Step 2 - Extract text before given string
The TEXTBEFORE function extracts a string before a specific substring from a given value.
TEXTBEFORE(input_text,text_after, [n], [ignore_case])
TEXTBEFORE(TEXTAFTER(B3, D3), D6)
becomes
TEXTBEFORE(" whale breathes air.", D6)
becomes
TEXTBEFORE(" whale breathes air.", "breathes")
and returns " whale ".
Use the TRIM function to remove leading and trailing spaces.
Useful links
TEXTAFTER function - Microsoft
Excel TEXTAFTER function: extract text after specific character or word
'TEXTAFTER' function examples
The following article has a formula that contains the TEXTAFTER function.
The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two […]
Functions in 'Text' category
The TEXTAFTER function function is one of many functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form