The TEXTBEFORE function
Everything before the at sign, what follows the last hyphen: what LEFT and FIND did with two nested functions.
Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
In a French Excel: =TEXTE.AVANT(texte; délimiteur; [no_instance]; [mode_correspondance]; [fin_correspondance]; [si_non_trouvé]).
Arguments
- text: the cell.
- delimiter: the separator.
- instance_num: the n-th occurrence; -1 for the last.
- if_not_found: the value to return instead of #N/A.
An example
=TEXTAFTER(A2,"-",-1)
What follows the last hyphen of A2: "0042" for "FR-2024-0042".
Pitfalls
- Separator missing: #N/A, unless the last argument is given.
- Microsoft 365 only.
Going further
Frequently asked questions
- How do I take the first name with TEXTBEFORE?
=TEXTBEFORE(A2," ",1,,,A2): what precedes the first space, or the whole text if there is none.