SubscribeExtract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]
![Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]](http://chandoo.org/img/f/username-from-email-id-excel-formula.png)
Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday?
So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID”
How is an email ID structured?
Any email ID contains 2 parts – user name and domain name.
For eg. in my email id – chandoo.d@gmail.com – chandoo.d is user name and gmail.com is domain.
So how do we get the user name out?
As you can see, username always starts at left and goes up to the symbol “@”. So, If we write a formula to fetch all the characters up to “@” symbol, it will get us the user name.
This is where LEFT() and FIND() formulas enter the scene.
What does Excel LEFT formula do?
Excel LEFT formula will let you cut a portion of text from left. For eg. =LEFT("Long",2) will give you Lo. (syntax and examples)
So, to get the email username, we need to get all the letters in the left of email ID up to the location of “@” symbol. And how do we find the position of a symbol in a text?
We use FIND formula.
FIND formula gives the location of one text in another. For eg. =FIND("do", "chandoo") will give us 5 (the location of “do” in “chandoo”).
FIND will throw an error (#VALUE!) if the text you are trying to find is not available. For eg. =FIND("peace", "world") will throw #VALUE!
Armed with these 2 formulas, now let us get that user name out of email ID
Assuming cell A1 has the email id, the formula for getting user name is =LEFT(A1,FIND("@",A1)-1)
We have to use -1 as find actually tells the position of “@” and we need all the letters up to “@”, but not “@”.
This is how it works:
Your homework:
- How would you extract the domain out of email ID? (Hint: there is a right formula for everything)
Use comments to write your answers. Don’t cheat.
Learn more excel formulas:
- 51 Excel Formulas in Plain English – Syntax, Examples and Explanation
- Excel Formulas & Working with Text
- Excel Formula Examples & Tutorials
- Learn Excel Formulas using my e-book – it is in a fun format & easy to understand
Trackbacks & Pingbacks
- Pingback by Great Trick: Extracting Domains from Email Addresses in Excel and SQL at Tom's Analytics on February 17, 2010 @ 12:09 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
I’ll just cheat and use text to columns instead
I forgot to post my non cheating attemt:
=RIGHT(B3;LEN(B3)-FIND(”@”;B3;1))
Hi Chandoo,
Nice post – I had a question – how do you create the visuals? i.e. how do you get the Excel sheet and the drag and drop etc? What tools did you use to achieve this?
Ubique
=RIGHT(A1,FIND(”@”,A1)-2)
One more option to extract the domain name.
=REPLACE(B3,1,FIND(”@”,B3),”")
Regards
You might also want to add TRIM to the formula so that embedded get cleaned up.
=RIGHT(A1,LEN(A1)-FIND(”@”,A1))
=RIGHT(A1,FIND(”@”,A1)-1)
Same as John’s except I don’t think the -2 is necessary vs. -1.
Here is how I would extract the domain name…
=MID(A1,FIND(”@”,A1)+1,999)
By the way, for those who might be interested, you can isolate the username from the email address when it is embedded in other text (this also works for the stand-alone situation too) using this formula…
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(”@”,A1)-1),” “,REPT(” “,99)),99))
You can isolate the domain name embedded in other text (this also works for the stand-alone situation too) using this formula…
=MID(A1,FIND(”@”,A1)+1,FIND(” “,A1&” “,FIND(”@”,A1))-FIND(”@”,A1)-1)
If there are more than one email address in the text, the above formulas return the username and domain name from the first one.
As with any solution to any problem there is always more than one way. I haven’t check all the solutions posted prior to mine but in looking over some of them, I think the posters did not check the solution either.
I went with a combination of RIGHT and LEN to find out the total numbers of characters up to and including the “@” symbol then subtract that form the total number of characters in the cell. That result is used to determine the number of charcaters from the right to display as the qualified domain name.
=RIGHT(A1,LEN(A1)-LEN(RIGHT(A1,FIND(”@”,A1))))
This is based on the cell only containing a standalone email address. If the text was part of a larger text string or sentence, then a solution like Rick Rothstein’s should be considered.
If there are more than one email address in the text and you want to extract the 2nd instance these formulas do the job.
To get the 2nd username
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(CHAR(160),SUBSTITUTE(A1,”@”,CHAR(160),2))-1),” “,REPT(” “,99)),99))
To get the 2nd domain
=LEFT(REPLACE(A1,1,FIND(CHAR(160),SUBSTITUTE(A1,”@”,CHAR(160),2)),”"),FIND(” “,REPLACE(A1,1,FIND(CHAR(160),SUBSTITUTE(A1,”@”,CHAR(160),2)),”")&” “)-1)
Regards.
@Ubique72: Thanks, I use Camtasia recorder to make screencasts like above. It is a fine software. You can get a free trail download from here: http://www.techsmith.com/download/camtasiatrialthx.asp
@John & Drew: There is an error with your formulas. FIND() actually tells the position of a first text in next one. So using FIND alone with RIGHT wouldnt work for us. you need to combine this information with the length (total number of characters in the email address) to extract the domain.
One correct way to do this is like this =RIGHT(B3,LEN(B3)-FIND(”@”,B3))
@Scaffdog845… You can simplify the formula by using just one RIGHT, LEN and FIND like this: =RIGHT(B3,LEN(B3)-FIND(”@”,B3))
@Loranga, Eric, Rick & Elias: Good solutions…
Hi !
a manupulation of the above technique -For splitting the name/text(alphanumeric) (two words with a space between) in two separate coloumns:
Illustration:
Column A Column B Column C
anurag gupta anurag gupta
Formula in Coloumn B
=IFERROR(LEFT(A6,FIND(” “,A6)-1),” “)
Formula in Colomun C
=IFERROR(TRIM(MID(A6,FIND(” “,A6),50)),” “)
Above is for 50 characters on right side of space , which can be customized.
Thanks a ton ! 2 chandoo … I was looking for this for any no. of characters either side of space..
Anurag
@Anurag,
A couple of questions…
1) In both your formulas… are you sure you want to return a space character if the ISERROR function evaluates to an error condition? Most people would return the empty string (”"… note, no space between the quote marks) for that condition.
2) For this formula…
=IFERROR(TRIM(MID(A6,FIND(” “,A6),50)),” “)
why are you using the TRIM function to remove the space the FIND function stopped on at the beginning of the returned text? You can eliminate the function call (in general, the less function calls, the more efficient the formula) by just adding a value of one to the FIND value…
=IFERROR(MID(A6,FIND(” “,A6)+1,50),”")
3. In the same formula… I think you are aware of this, but just to make sure… you do know that you can use any large number in place of the 50, right? The general rule is to use a number that you know will be larger than the longest text string you plan to have returned to you… the MID function will not be confused if you specify a length in the 3rd argument that is longer than the actual text string.
Hi Rick !
Thanks for observation and suggestion !!!! nothing was intensional in both formula ! was just a try …your suggestions well taken ….
Am novice and still learning …
Thanks
I did just like Loranga.
Which way is the best?
Thanks Chandoo!