Excel TEXTBEFORE function – extract text before character (delimiter)
In this tutorial, you’ll learn how to use the TEXTBEFORE function in Excel to quickly get the text that comes before a certain character or word.
In older versions of Excel, this was harder to do. You had to first find the position of the character (like a space or comma) using SEARCH or FIND, and then use the LEFT function to get the text before it.
But in Excel 365 and Excel for the web, there’s a new and much easier way — the TEXTBEFORE function does it for you in one step!
Table of Contents
Excel TEXTBEFORE function
The TEXTBEFORE function in Excel helps you get the text that comes before a specific character or word (called a delimiter).
If the delimiter appears more than once in the cell, you can choose which one to use. If the delimiter isn’t found, you can show your own message or return the full original text.
This function has 6 parts, but only the first two are required.
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
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.
- 0 (default) – case-sensitive
- 1 – case-insensitive
match_end (optional) – treats end of text as delimiter. Disabled by default.
- 0 (default) – match the delimiter exactly as specified in the formula.
- 1 – match the delimiter against the end of text. Practically, it means that if the delimiter is not found, the function will return the original text.
if_not_found (optional) – the value to return if the delimiter is not found. If not set, a #N/A error is returned.
TEXTBEFORE availability
Currently, the TEXTBEFORE function is only available in Excel for Microsoft 365, Excel 365 for Mac, and Excel for the web.
Excel formula to extract text before character
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.
Extract text before first space in Excel
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.
Get text before Nth instance of delimiter
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)
Return text before last occurrence of delimiter
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)
Extract text before substring
The delimiter in the TEXTBEFORE function can be a substring, which means any sequence of characters. This makes the function even more useful!
Let’s say you have a list of employee names and their positions in one column, with a hyphen separating them. The problem is that some names have hyphens in them (like “Mary-Jane”). To avoid splitting these names, you can use a substring that includes a space and a hyphen (” -“) as the delimiter.
Here’s the formula:
=TEXTBEFORE(A2, ” -“)
This will correctly separate the name and position without cutting hyphenated names in half.
Get text before multiple delimiters
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 delimiter is not found, return custom text
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, “,”, , , , “”)
If delimiter is not found, return original text
Sometimes, it’s better to show the original text if the TEXTBEFORE function can’t find the character or word you’re looking for.
To do this, set the 5th argument (called match_end) to 1. This tells Excel to treat the end of the text as a fallback delimiter.
=TEXTBEFORE(A2, “,”, , ,1)
In this example, if there’s no comma in the cell, the formula will simply return the full text (like the employee’s name).
Make delimiter case-sensitive or case-insensitive
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)
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!
