TEXTBEFORE
Return the text that comes before a chosen character or word. Great for splitting strings without nesting LEFT, FIND, and LEN.
Spreadsheet editor
Spreadsheet editor
Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Returns: Text Arguments
| Argument | Required | Description |
|---|---|---|
| text | Yes | The text you want to search inside. This can be typed in quotation marks or a reference to a cell that holds the text. Wildcards are not allowed. |
| delimiter | Yes | The character or word that marks where to split. TEXTBEFORE returns everything that comes before it. |
| instance_num | No | Which occurrence of the delimiter to split on. Defaults to 1 (the first). Use a negative number to count from the end, so -1 finds the last one. It cannot be 0. |
| match_mode | No | Set to 0 (the default) for a case-sensitive match, or 1 to ignore case. |
| match_end | No | Set to 1 to treat the very end of the text as an extra delimiter. This only matters in an edge case: if you ask for an instance of the delimiter that doesn't exist (like the 2nd dash when there's only one), TEXTBEFORE uses the end of the text as that delimiter and returns the whole text instead of a #N/A error. Defaults to 0. |
| if_not_found | No | The value to return when the delimiter isn't found. By default TEXTBEFORE returns a #N/A error. |
About
TEXTBEFORE pulls out everything that comes before a delimiter you pick, such as a comma, a space, or a whole word. You give it the text, tell it what to look for, and it hands back the part that sits in front. It saves you from the old habit of stacking LEFT, FIND, and LEN together just to grab the front of a string.
Use it when you need the first half of a value: the user name before the @ in an email, the street before a comma in an address, or the year before a dash in a date code. The optional instance number lets you choose which delimiter to split on, and a negative number counts from the end, so grabbing the text before the last occurrence is easy. You can also make the match case-insensitive or treat the end of the text as a delimiter.
TEXTBEFORE works in Microsoft 365 and Excel 2024. On older versions you can recreate it by combining LEFT with FIND, or SEARCH when you need a case-insensitive match. To grab the part after the delimiter instead, use TEXTAFTER, and clean up any stray spaces with TRIM.
Use it when you need the first half of a value: the user name before the @ in an email, the street before a comma in an address, or the year before a dash in a date code. The optional instance number lets you choose which delimiter to split on, and a negative number counts from the end, so grabbing the text before the last occurrence is easy. You can also make the match case-insensitive or treat the end of the text as a delimiter.
TEXTBEFORE works in Microsoft 365 and Excel 2024. On older versions you can recreate it by combining LEFT with FIND, or SEARCH when you need a case-insensitive match. To grab the part after the delimiter instead, use TEXTAFTER, and clean up any stray spaces with TRIM.
Examples
Drop the size from a product code
See how -1 finds the last dash in a code, so you always trim off the final part. Add or remove sections in column A and watch the result follow the last dash.
Spreadsheet editor
Avoid #N/A when the delimiter is missing
Watch what happens when an address has no @ sign. The plain formula returns #N/A, but the if_not_found argument falls back to the original text. Edit an address to remove its @ and compare the two columns.
Spreadsheet editor
Match the delimiter no matter the case
See how the default split misses an uppercase X and returns #N/A. Set match_mode to 1 to ignore case and grab the width every time. Switch a size between x and X to watch both columns react.
Spreadsheet editor
Watch out for
⚠
#N/A when the delimiter isn't there
If the delimiter never appears in the text, TEXTBEFORE returns a #N/A error by default, which can break the rest of your sheet.
→ Set the if_not_found argument to a fallback value, like =TEXTBEFORE(A2, "@", 1, 0, 0, "") to return a blank instead. You can also wrap it in IFERROR.
⚠
Case-sensitive matching by surprise
By default the match is case-sensitive, so splitting on "x" will skip an uppercase "X" and may miss the spot you wanted.
→ Set match_mode to 1 to ignore case, like =TEXTBEFORE(A2, "x", 1, 1).
⚠
A trailing space sneaks into the result
Splitting "London , UK" on just "," leaves the result ending with a space.
→ Include the space in the delimiter (" ,") or clean the result with TRIM.
Tips & notes
Use a negative instance_num to work from the right side of the text, which is the easiest way to split on the last occurrence of a delimiter. TEXTBEFORE does not accept wildcards in the delimiter. Pair it with TEXTAFTER to grab both halves of a value, and wrap it in IFERROR to keep a missing delimiter from breaking the rest of your sheet.
Common questions
How do I get the text before the last occurrence of a character?
Use a negative instance number. =TEXTBEFORE(A2, "-", -1) returns the text before the final dash, which is handy for product codes and file names.
How is TEXTBEFORE different from LEFT?
LEFT pulls a fixed number of characters from the start. TEXTBEFORE finds the position for you by searching for a delimiter, so you don't have to calculate how many characters to take.
Why does TEXTBEFORE return #N/A?
It can't find the delimiter in your text. Add the if_not_found argument to return a fallback value, or check that the delimiter actually appears and matches the right case.