TEXTBEFORE function
TEXTBEFORE returns everything that comes before a chosen delimiter, such as the username in an email address. It is easier than combining LEFT with FIND, and returns #N/A if the delimiter is missing.
Example
Read it as a sentence: return everything before the second hyphen in a-b-c, which gives a-b.
A
B
C
D
1
a-b
2
3
Syntax
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Arguments in [square brackets] are optional.
-
text(required) - The text to search.
-
delimiter(required) - The marker to look for.
-
instance_num(optional) - Which occurrence; negative counts from the end. Default 1.
-
match_mode(optional) - 0 case-sensitive (default), 1 ignore case.
-
match_end(optional) - 1 treats the end of text as a delimiter.
-
if_not_found(optional) - Value to return instead of #N/A.
Returns: The text preceding the delimiter.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TEXTBEFORE("a-b-c","-") | a | First |
=TEXTBEFORE("a-b-c","-",2) | a-b | Second instance |
=TEXTBEFORE("a-b-c","-",-1) | a-b | From end |
=TEXTBEFORE("a-b-c","_",,,,"none") | none | if_not_found |
=TEXTBEFORE("aXb","x",,1) | a | Case-insensitive |
=TEXTBEFORE("a-b","-",-2,,1) | match_end from start | |
=TEXTBEFORE("a-b-c","_") | #N/A | Not found |
=TEXTBEFORE("a-b","-",0) | #VALUE! | Instance 0 |
Watch out for
- Returns #N/A if the delimiter is missing.
- Instance 2 returns the text before the second delimiter, including the first part.
Where it works
- Excel
- TEXTBEFORE arrived in Excel 2024. Older versions do not have it. Excel 2024 and Microsoft 365.
- Google Sheets
- No direct equivalent. Use REGEXEXTRACT or LEFT/FIND.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .