Function Reference: TEXTAFTER, TEXTBEFORE
| Syntax | ||
| =TEXTAFTER(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]) =TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]) |
||
| Purpose / Description | ||
| Returns the texts string after or before the given instance of a delimiter in a text string. How the match is made is controlled by optional arguments, and an optional argument specifies what to return if the delimiter is not found. | ||
| Arguments | ||
| Name | Type | Description |
| text | text string | A reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a text string. The normal coercion rules apply if a number or logical value is passed to this argument. |
| delimiter | text string or array of text strings | A reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a text string. The text string is commonly one character, but may be two or more characters, which delimits the sub-parts within text. If an array of values is passed, the value returned will be that before or after the first or last of the delimiters, depending upon the other search parameters. So an array of {" ",".","-"} will seek the first or last of either a space, a period or a dash within text, whichever occurs first or last, depending upon the search order.. |
| [instance_num] | number | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a number. If the value is not an integer, the value is truncated to an integer. Positive values count the instances of delimiter from the front of the string, negative values from the end of the string. The value must be ≤ -1 or ≥ 1. |
| [match_mode] | number | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a number 0 or 1. If the value is not a number, text strings and logical values are coerced in the normal way. The value must be 0 ≤ n < 2. If the value is not an integer, the value is truncated to an integer. If the value is 0 or omitted, the matching for delimiter is case-sensitive. If the value is 1, the matching for delimiter is case-insensitive. |
| [match_end] | number | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a number 0 or 1. If the value is not a number, text strings and logical values are coerced in the normal way. The value must be 0 ≤ n < 2. If the value is not an integer, the value is truncated to an integer. If the value is 0 or omitted, an extra delimiter is not added to the end (positive values of instance_num) or beginning (negative values of instance_num) of text.. If the value is 1,,an extra delimiter is added to the end (positive values of instance_num) or beginning (negative values of instance_num) of text.. |
| [if_not_found] | any | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a value to be used as the return value if the delimiter is not found in text. If omitted, defaults to #N/A. But see errors below. |
| Return Values | ||
| Type | Specific Value | Description |
| text string | The text string which is the contents of text that occur after (TEXTAFTER) or before (TEXTBEFORE) instance_num of delimiter in text. IF delimiter is an empty string, or is omitted, then the following rules apply: TEXTAFTER: if instance_num is positive it returns the original string text, if it is negative, it returns an empty string. The absolute numeric value is ignored, only the sign of instance_num is evaluated. TEXTBEFORE: if instance_num is positive it returns an empty string, if it is negative, it returns the original string text. The absolute numeric value is ignored, only the sign of instance_num is evaluated. |
|
| error | #N/A | If the nominated instance_num of delimiter is not found in text. But see the next point. |
| error | #VALUE! | If the absolute value of instance_num is greater than the count of delimiters in text + 1 then the #VALUE! error is returned, regarless of whether or not if_not_found is provided. |
| error | Any errors referenced by arguments are returned due to propagation of error values.. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | These functions have vastly simplified extracting parts of a string, especially where the later sub-parts are required and complex formulas were previously required. | |
| Examples: | Open / download sample workbook: TEXTAFTER, TEXTBEFORE, TEXTSPLIT.xlsx | |