TEXTBEFORE and TEXTAFTER in Excel: how to use it
TEXTBEFORE gives everything before a delimiter and TEXTAFTER everything after it. Available in Excel 365.
Try the first exercises for freeIn the lesson TEXTBEFORE and TEXTAFTER
What do TEXTBEFORE and TEXTAFTER do?
TEXTBEFORE gives everything before a delimiter, TEXTAFTER everything after it. You only say at which character Excel should cut, such as an @, a space or a hyphen.
In the past, you needed LEFT or MID with SEARCH for that. TEXTBEFORE and TEXTAFTER do it in one step, but they only work in Excel 365. If your file also has to work in an older version, use the old way.
When do you use TEXTBEFORE and TEXTAFTER?
- The name before the @ in an email address: =TEXTBEFORE(A2,"@").
- The domain after the @: =TEXTAFTER(A2,"@").
- The first name from a full name: =TEXTBEFORE(A2," ").
- The last piece of a code, after the last hyphen: =TEXTAFTER(A2,"-",-1).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Everything before the delimiter.
Filled in: =TEXTBEFORE(A2," ")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TEXTBEFORE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TEXTBEFORE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The cell with the text. | A2 |
delimiter | The character where Excel cuts, such as "@", " " or "-". | " " |
[instance_num] | Optional: cut at that delimiter, such as 2 for the second. With -1, you cut at the last one. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
TEXTBEFORE and TEXTAFTER step by step
C1 contains =TEXTAFTER(A1,"@"). Everything after the @. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type TEXTAFTER with a bracket, followed by the cell with the email address, A1.
=TEXTAFTER(A1 - Type a comma and, in quotation marks, the character where Excel should cut: "@".
=TEXTAFTER(A1,"@" - Close the bracket and press Enter: you get everything after the @.
=TEXTAFTER(A1,"@")
More examples
The first name
C1 contains =TEXTBEFORE(A2," "). Everything before the first space: the first name.
The last piece
C1 contains =TEXTAFTER(A2,"-",-1). With -1, Excel cuts at the last hyphen.
The piece between two characters
C1 contains =TEXTBEFORE(TEXTAFTER(A2,"-"),"-"). First everything after the first hyphen, MAN-07, and from that everything before the next hyphen.
Where it often goes wrong
- If the character isn't there, you get #N/A.
Practise this in the lesson TEXTBEFORE and TEXTAFTER. The first exercises are free.
Frequently asked questions
Can I pick the last delimiter instead?
Yes, in Excel 365 with a negative instance number as the third argument.
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND