This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
Excel function · Changing text
TEXTE.AVANT(TEXTBEFORE)
Returns the text before a given separator.
What it does
TEXTBEFORE returns everything before a separator such as @, a hyphen or a space. You don't need to count characters, so it works whatever the length of the parts.
Syntax
=TEXTE.AVANT(text; delimiter; [instance_num]; [match_mode]; [match_end]; [if_not_found])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;
Arguments
- text
- The text to take the part from.
- delimiter
- The separator to look for, in quotes.
- instance_num
- Which occurrence of the separator to use. 1 (the default) is the first; -1 is the last.
- match_mode
- 0 (the default) is case-sensitive; 1 ignores case.
- match_end
- 1 treats the end of the text as a separator. Leave it out in most cases.
- if_not_found
- What to return when the separator isn't there, instead of
#N/A.
Example
What is the user name in this email address?
| A | B | |
|---|---|---|
| 1 | User name | |
| 2 | ana.ruiz@example.com | ana.ruiz |
The formula in B2:
=TEXTE.AVANT(A2;"@")Result: ana.ruiz
The part before the @ in ana.ruiz@example.com is ana.ruiz. The dot doesn't matter because only @ is the separator.
Common mistakes
- Not planning for a missing separator, which gives
#N/A. Use the last argument to return something friendlier. - Wanting the part before the last separator but leaving out the instance number. Use -1.
- Using it in a file that must open in older Excel versions, which don't have it.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | TEXTBEFORE | , |
| Deutsch | TEXTVOR | ; |
| Français | TEXTE.AVANT | ; |
| Español (España) | TEXTOANTES | ; |
| Español (México) | TEXTOANTES | , |
| 日本語 | TEXTBEFORE | , |
| 한국어 | TEXTBEFORE | , |
| 繁體中文 | TEXTBEFORE | , |
| ไทย | TEXTBEFORE | , |
| ខ្មែរ | TEXTBEFORE | , |
| Tiếng Việt | TEXTBEFORE | ; |