TEXTAFTER

TEXTAFTER(text, delimiter [,instance_num] [,match_mode] [,match_end] [,not_found])

Returns the characters from the end of a text string after a delimiter.

textThe text you are searching within.
delimiterThe text that marks the point after which you want to extract.
instance_num(Optional) A number indicating which instance of the delimiter to use (default is 1)
match_mode(Optional) A logical value indicating whether to ignore the case:
0 = Case sensitive (default)
1 = Not case sensitive
match_end(Optional) Treats the end of the text as the delimiter:
0 = Do not use the end as a delimiter (default)
1 = Use the end as a delimiter
not_found(Optional) The value to return when no match is found (default is #N/A).

REMARKS
* This function was added in Microsoft 365.
* This function is case sensitive by default but this can be changed using the "match_mode" argument.
* This function does not support wildcards (? and *).
* If "instance_num" is left blank, then 1 is used.
* If "instance_num" < 1, then searching starts at the end of the string.
* If "instance_num" = -1, then the text after the last delimiter is returned. Example 12.
* If "instance_num" = 0, then #VALUE! is returned.
* If "instance_num" > the number of occurrences of the delimiter, then #N/A is returned.
* If "instance_num" > the length of the text, then #VALUE! is returned.
* If "match_mode" is left blank, then 0 is used (case sensitive).
* If "not_found" is left blank, then #N/A is used.
* If you want to use more than one delimiter you can pass in an array {":", ";", "-"} as the delimiter.
* If you want to return the original text when the delimiter cannot be found, use "instance_num =-1" and "match_end=1".
* You can use the FIND function to return the starting position of a substring. This is case sensitive.
* You can use the SEARCH function to return the starting position of a substring. This is not case sensitive.
* You can use the TEXT function to return the number as a formatted text string.
* You can use the TEXTBEFORE function to return the characters from the start of a text string before a delimiter.
* You can use the TEXTJOIN function to return the text string that is a concatenation of several strings with a delimiter.
* You can use the TEXTSPLIT function to return the text string split into multiple columns using delimiters.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

 A
1=TEXTAFTER("better solutions", " ") = solutions
2=TEXTAFTER("better solutions", "t") = ter solutions
3=TEXTAFTER("better solutions", "t", 1) = ter solutions
4=TEXTAFTER("better solutions", "t", 2) = er solutions
5=TEXTAFTER("better solutions", "t", 3) = ions
6=TEXTAFTER("better solutions", "t", 3, 0) = ions
7=TEXTAFTER("better solutions", "t", 3, 1) = ions
8=TEXTAFTER("better solutions", "T", 3) = #N/A
9=TEXTAFTER("better solutions", "T", 3, 0) = #N/A
10=TEXTAFTER("better solutions", "T", 3, 1) = ions
11=TEXTAFTER("a-b-c-d", "-", 1, 0, 0) = b-c-d
12=TEXTAFTER("a-b-c-d", "-", -1, 0, 0) = d
13=TEXTAFTER("a-b-c-d", "-", -4, 0, 1) = a-b-c-d
14=TEXTAFTER("a-b-c-d", "-", -4, 0, 0) = #N/A
15=TEXTAFTER("a-b-c-d", " ", 0) = #VALUE!

1 - What is the text after the first space.
2 - What is the text after the first "t".
3 - What is the text after the first "t".
4 - What is the text after the second "t".
5 - What is the text after the third "t". Case sensitive.
6 - What is the text after the third "t". Case sensitive.
7 - What is the text after the third "t". Not case sensitive.
8 - What is the text after the third "T". Case sensitive.
9 - What is the text after the third "T". Case sensitive.
10 - What is the text after the third "T". Not case sensitive.
11 - What is the text after the first delimiter, counting forwards.
12 - What is the text after the first delimiter, counting backwards (or the last delimiter, counting forwards).
13 - What is the text after the fourth delimiter, counting backwards using the end as a delimiter.
14 - What is the text after the forth delimiter, counting backwards not using the end as a delimiter.
15 - What happens when the "instance_num" is zero.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top