The TEXTBEFORE function
Returns the text before a delimiter, such as everything before the @ in an email address. You can choose which occurrence of the delimiter to use.
Syntax
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])- text
- The text to search.
- delimiter
- The character or text to stop at.
- instance_num (optional)
- Which occurrence to use. 1 or left out for the first, 2 for the second, -1 for the last.
- match_mode (optional)
- 1 to ignore upper and lower case. Left out, 0, which matches case.
- match_end (optional)
- 1 to treat the end of the text as a delimiter. Left out, 0.
- if_not_found (optional)
- What to return when the delimiter is missing. Left out,
Examples
C1
=TEXTBEFORE(1A2,2"@")| A | B | C | |
|---|---|---|---|
| 1 | ann.berg | ||
| 2 | ann.berg@example.com |
In plain words: Return the text in 1the email address in A2 that comes 2before the @.
B1=TEXTBEFORE("2024-03-15","-",2)
| A | B | |
|---|---|---|
| 1 | 2024-03 |
B1=TEXTBEFORE("C:/Reports/2024/sales.xlsx","/",-1)
| A | B | |
|---|---|---|
| 1 | C:/Reports/2024 |
Practice TEXTBEFORE
Questions
Why does TEXTBEFORE return
The delimiter is not in the text. Give a fallback as the last argument, as in =TEXTBEFORE(A2," ",,,,A2), to return the whole text when there is no space.
How do I do the same in older versions of Excel?
Combine LEFT and FIND, as in =LEFT(A2,FIND("@",A2)-1). FIND gives the position of the @, and LEFT takes everything before it.