Excel functions / Text
TEXTBEFORE & TEXTAFTER
Return the text before or after a delimiter, such as a space, comma or @ sign.
Syntax
=TEXTBEFORE(text, delimiter, [instance_num])
=TEXTAFTER(text, delimiter, [instance_num])Arguments
text | The text to split. |
|---|---|
delimiter | The character or text to split at. |
instance_num | Optional. Which occurrence to use. Negative numbers count from the end, so -1 is the last. |
How it works
These two functions make splitting text easy. =TEXTBEFORE(B2,"@") returns the part of an email before the @, and =TEXTAFTER(B2,"@") returns the domain.
The instance number picks which delimiter to split at when there are several. They are available in Microsoft 365 and Excel for the web.
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | |||
| 2 | Nina Shah | nina.shah@example.com | example.com | |
| 3 | Omar Reid | omar@example.com | ||
| 4 | Mary Ann Lee | mary.lee@example.com | ||
| 5 |
Result: example.com. Everything after the @ sign: the email domain.
Tips
- If the delimiter is not found, they return #N/A. On older Excel, use LEFT, MID and FIND instead.
Practise TEXTBEFORE & TEXTAFTER
Try it yourself in a real spreadsheet, with instant feedback on every answer.