Take the name before the @
PremiumIn B2, get the part of the email in A2 before the @. Use LEFT, with FIND to work out how many characters to take.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | User name | |||||
| 2 | anna.berg@northwind.com | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock FIND and SEARCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:Joining text with & and TEXTJOINSUBSTITUTE, TEXTBEFORE and TEXTAFTER
Need help?
Why this works
FIND("@",A2) gives 10, the position of the @. The name is everything before it, so LEFT takes 10 - 1 = 9 characters: "anna.berg". Fill the formula down and it works for names of any length.
Common mistake: Forgetting the -1. LEFT then takes the @ as well.
The answer
- B2
=LEFT(1A2,2FIND("@",A2)-1)
In plain words: Take 2everything before the @ from the start of 1the email in A2.
=LEFT(A2,FIND("@",A2)-1) returns everything before the first @. In newer Excel, =TEXTBEFORE(A2,"@") does the same.