Extract text after a delimiter
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])| Parameter | Description |
|---|---|
text |
The text string to extract from. |
delimiter |
The character(s) that delimit the text. |
instance_num |
[optional] The instance of the delimiter in text. Default is 1. |
match_mode |
[optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0. |
match_end |
[optional] Treat end of text as delimiter. 0 = disabled, 1 = enabled. Default is 0. |
if_not_found |
[optional] Value to return when no match is found. #N/A is default. |
To extract the text that occurs after a specific character or substring, provide the text and the character(s) to use as delimiter in double quotes ("
=TEXTAFTER("Jones,Bob",",") // returns "Bob"You can use more than one character for delimiter. For example to extract the second dimension in the text string "12 ft x 20 ft", use " x "for delimi
=TEXTAFTER("12 ft x 20 ft"," x ") // returns "20 ft"The formulas below extract text after the first and second occurrence of the hyphen character ("-"):
=TEXTAFTER("ABX-112-Red-Y","-",1) // returns "112-Red-Y"
=TEXTAFTER("ABX-112-Red-Y","-",2 // returns "Red-Y"This is very handy because you don't need to know how many words are in the sentence to begin with. The formulas below extract text after the last and
=TEXTAFTER("ABX-112-Red-Y","-",-1) // returns "Y"
=TEXTAFTER("ABX-112-Red-Y","-",-2) // returns "Red-Y"Normally, TEXTAFTER does not treat the end of a text string as a delimiter. For example, the formula below asks for the text after delimiter 3, counti
=TEXTAFTER("ABX-123-Red-XYZ","-",-3) // returns "123-Red-XYZ"And this formula returns #N/A because there is no fourth delimiter from the end:
=TEXTAFTER("ABX-123-Red-XYZ","-",-4) // returns #N/A