Learning Excel: TEXTBEFORE and TEXTAFTER
Before and after a character
LEFT, SEARCH and a -1: it works, but Natalie frowns at all those brackets. Newer versions of Excel have two functions for it that say straight away what they do.
TEXTBEFORE and TEXTAFTER
TEXTBEFORE gives everything before a delimiter, TEXTAFTER everything after it. =TEXTBEFORE(C2,"@") gives sadie.devere, no counting needed.
Both exist in Excel 365 and Excel 2024 or later. In older versions you keep using LEFT and SEARCH.
=TEXTBEFORE(C2,"@")
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | sadie.devere |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
Counting from the end
A third argument says which delimiter you mean: the first, the second, and so on. A negative number counts from the end.
=TEXTAFTER(C2,".",-1) gives everything after the last dot: uk.
=TEXTAFTER(C2,".",-1)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | uk |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
Try it yourself
In G2, put the domain of Sadie's address, everything after the @.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | ||
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | ||
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | ||
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | ||
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | ||
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | ||
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | ||
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | ||
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | ||
| 10 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson