by Alexander Frolov, updated on
In this tutorial, we will explore the Excel TEXTBEFORE function and how it can help you quickly extract text before any character or substring that you specify.
In earlier Excel versions, extracting text before a space, comma or some other character was quite tricky. First, you had to determine the position of the delimiter in a string using the SEARCH or FIND function, and then get all the characters before it with the help of LEFT. In Excel 365 and Excel for the web, we now have a brand-new TEXTBEFORE function that does the magic!
The TEXTBEFORE function in Excel is specially designed to return the text that occurs before a given character or substring (delimiter). In case the delimiter appears in the cell multiple times, the function can return text before a specific occurrence. If the delimiter is not found, you can return your own text or the original string.
The syntax of the TEXTBEFORE function has 6 arguments, of which only the first two are required.
text (required) - the original text to extract from. Can be supplied as a string or cell reference.
delimiter (required) - a character or substring that delimits the text.
instance_num (optional) - the instance of the delimiter before which to extract text. Default is 1. A negative number starts searching from the end of the original text.
match_mode (optional) - determines the delimiter's case-sensitivity. Enabled by default.
match_end (optional) - treats end of text as delimiter. Disabled by default.
if_not_found (optional) - the value to return if the delimiter is not found. If not set, a #N/A error is returned.
Currently, the TEXTBEFORE function is only available in Excel for Microsoft 365, Excel 365 for Mac, and Excel for the web.
Tips:
For starters, let's get to know how to build a TEXTBEFORE formula in its simplest form.
Supposing you have a list of full names in column A and want to extract the first name that appears before the comma.
That can be done with this basic formula:
=TEXTBEFORE(A2, ",")
Where A2 is the original text string and a comma (",") is the delimiter.
To get text before a space in a string, just use the space character for the delimiter (" ").
=TEXTBEFORE(A2, " ")
Since the instance_num argument is set to 1 by default, the formula will return text that appears before the first space.
Tip. To extract text before the Nth space, define the occurrence number using the 3rd (optional) instance_num argument.
To extract text that appears before the nth occurrence of the delimiter, supply the number for the instance_num parameter.
For example, to get text before the second occurrence of a comma, the formula is:
=TEXTBEFORE(A2, ",", 2)
Tip. If the specified occurrence is not found, TEXTBEFORE returns a #N/A error. Instead, you can configure the if_not_found argument to return the text you specify or set match_end to 1 to output the original text.
To return text before the last occurrence of the specified character, put a negative value in the instance_num argument.
For example, to return text before the last comma in A2, the formula is:
=TEXTBEFORE(A2, ",", -1)
To extract text before the last but one comma, set instance_num to -2:
=TEXTBEFORE(A2, ",", -2)
The delimiter argument of TEXTBEFORE can be a substring, i.e. a sequence of characters, which adds even more value to the function.
Let's say you have a list of employee names and their positions in one column, separated by a hyphen. The problem is that some names are hyphenated. To avoid splitting such names, we use a substring consisting of a space and a hyphen (" -") for the delimiter:
=TEXTBEFORE(A2, " -")
To handle different variations of the delimiter, you can use an array constant like {"a","b","c"} in your TEXTBEFORE formula, where a, b, and c represent different delimiters.
For example, if the delimiter is either a comma or a hyphen, with or without the preceding space character, you can handle all four variations correctly by providing the array constant {","," ,","-"," -"} for delimiter:
=TEXTBEFORE(A2, {","," ,","-"," -"})
If the specified delimiter is not found, the TEXTBEFORE function returns a #N/A error by default. To return a custom value instead, configure the if_not_found argument (the last one).
For example, if not a single comma appears in the source text (A2), this basic formula throws a #N/A error:
=TEXTBEFORE(A2, ",")
To get rid of the errors, type any text you want in the final argument, e.g. "Not found":
=TEXTBEFORE(A2, ",", , , , "Not found")
To return nothing, i.e. a blank cell, use an empty string for if_not_found:
=TEXTBEFORE(A2, ",", , , , "")
In some situations, it makes sense to return the original text if the TEXTBEFORE function cannot match the delimiter in the string. For this, just set the match_end argument (the 5th one) to 1. This will tell the formula to treat the end of text as delimiter.
=TEXTBEFORE(A2, ",", , ,1)
As you can see in the screenshot below, the formula now returns an employee name even if the delimiter (which is a comma) is not present in a cell.
By default, the Excel TEXTBEFORE function is case-sensitive, meaning it treats lowercase and uppercase delimiters as different ones. To disable case-sensitivity, set the match_mode argument (the 4th one) to 1 or TRUE.
For example, the below formula only accepts the lowercase "x" as the delimiter:
=TEXTBEFORE(A2, "x")
Whereas an analogous formula with match_mode set to 1, recorgnizes both "x" and "X" as the delimiter:
=TEXTBEFORE(A2, "x", ,1)
If the TEXTBEFORE function does not work as expected or is not available in your Excel, it's likely to be one of these reasons.
If TEXTBEFORE does not exist in your Excel, please check out the list of supported versions.
If TEXTBEFORE returns a #N/A error, then either:
If TEXTBEFORE returns a #VALUE! error, then either:
That's how to extract text before character in Excel 365 with the help of the TEXTBEFORE function. I thank you for reading and hope to see you on our blog next week!
TEXTBEFORE function – formula examples (.xlsx file)
Table of contents