The TEXTBEFORE function is a new function added to Excel in September 2022.
It allows you to specify the characters to search for and retrieve the text up to those characters.
This function is the reverse function of TEXTAFTER.
Previously, you had to combine FIND and LEFT, but now you only need one function.
How it works
=TEXTBEFORE(Text,Delimiter,Instance_num,Match_mode,Match_end,If_not_found)
| Name | Omission | Explanation |
|---|---|---|
| Text | Required argument. Specify text. | |
| Delimiter | Required argument. Specify the characters to search for. Two or more characters are also acceptable. | |
| Instance_num | 1 | Specifies the number of Delimiter. If omitted, it is 1. Text is obtained up to the first Delimiter. If a negative number is specified, it is the number of pieces from the right. |
| Match_mode | 1 | Specifies whether to be case-sensitive. 0: Case sensitive. 1: Not case sensitive. |
| Match_end | 0 | 0: Performs a normal search. 1：The search is performed on the assumption that there is a delimiter at the end of the text. If there is no delimiter in the text, the full text is the first result. |
| If_not_found | #N/A | Specifies the result if the delimiter is not present. |
Demonstrate
Basically, the first argument specifies the text.
The second argument specifies the characters to be searched.
The second argument can be two or more characters.
Argument 3: Instance_num
The third argument specifies the number of Delimiter to be obtained.
If omitted, it is set to 1. Up to the first Delimiter.
If a negative number is specified, the number of pieces will be from the right.
Argument 4: Match_mode
The fourth argument specifies whether it is case-sensitive.
- 0: Case sensitive.
- 1: Not case sensitive.
If 1 is specified, the first result is the full text.
Argument 5: Match_end
The fifth argument specifies the search method.
When using a formula that combines FIND and LEFT to retrieve the last element, it was necessary to be aware of the end and use the LEN function to separate cases, but with TEXTBOFORE, it is possible to formulate a concise formula without thinking about this.
- 0: Performs a normal search.
- 1：The search is performed on the assumption that there is a delimiter at the end of the text.If there is no delimiter in the text, the full text is the first result.
If 1 is specified, the search is performed assuming that Delimiter exists at the end of the text.
Argument 6:If_not_found
The sixth argument specifies the result if not found.
Spill
If the argument were a cell range, it would be Spill .
You would mainly make "Text" a cell range.
---