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
| ||||
|
| ||||
|
Leave a Reply
 |
Best Sales Dashboards, as Voted by You [Visualization Challenge #2 - Winners] | Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
38 Responses to “Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]”
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!
[...] and powerful. I hope this is useful to someone – the inspiration for this post came from this post at Chandoo.org. Pointy Haird Dilbert is easily my favorite as well as one of the most useful and entertaining [...]
Hi All. I need help to add 1st name, last name with @ domain. Please help.
@RZX… I think you will need to provide some more information in order for someone to be able to help you. Where are your first, last and domain names at? What do you want the final result to look like? If you can give an example or two for these questions, that might help us narrow down a solution for you.
@RZX… if the first name, last name and domain are in cells A1, B1 and C1 then write
=a1&”.”&b1&”@”&c1 to generate email id like
firstname.lastname@domain
Hi, I am trying to extract the first name from the email id which is in this format: firstname.lastname@domain.com, and I used your formula to show =left(A2,find(“.”,A2)-1)
and ended up with no value. What am I doing wrong here?
@Dumbee1018: Welcome to Chandoo.org and thanks for your comments. The formula works alright for me. Can you give me some sample data so that I can verify?
chandoo the formula which you give is just a waste of time
we know this .
@Dumbee1018
When I copied the above I got an error
I retypes the Quote Symbols ” manually and all was fixed
The Quote symbols neeed to be the vertical ones Shift ‘ not the angled ones
Try this to extract domain name “=TRIM(MID($A9,SEARCH(“@”,$A9,1)+1,500))” . I assume the domain name cannot be more than 500 characters long.
This should do for the user name “=TRIM(MID($A9,1,SEARCH(“@”,$A9,1)-1))”
how to get only last name, when the id is firstname.lastname@domain.com
@Daya
=TRIM(MID(A1,FIND(“.”,A1,1)+1,FIND(“@”,A1,1)-FIND(“.”,A1)-1))
~Vijay
Dear Gurus,
I have a problem, i have a specific word “XYZ” in a between a string of characters in different rows. Corresponding to these string rows i have numerical values. I have to add the numerical values on the occurrence of the specific word anywhere between the string. How do i do this addition? Any suggestions?
Awsum tuts man..it really help me a lot. Work Simplified !!!
Gr8 going.
I have a long list of emails that I need to add: @xxxxxxx.com to. How can I do this without merging cells? I know there’s a way!
Alice,
If you have a list of usernames, use the &”@xxxxxxx.com”. Assuming your list begins in A1 and contains “johndoe”, use =A1&”@xxxxxxx.com” in B1. The result of the formula would be johndoe@xxxxxxxx.com. Using the fill handle on B1, drag down to the bottom of your list of usernames. If the list of usernames is contiguous, double-click on the fill handle to quickly do the same job. Select and copy the cells in column B, and do a special paste starting in C1 to convert the cells to values (Alt,E,S,V).
Is there a way to extract the user name to 2 other cells? For example John.Doe@company.com is in A1 and I need to have John populate in B1 (which is all first names) and Doe in C1(which is all last names). Another issue is my list has middle initials too, for instance Jane.G.Doe@company.com I just want Jane G om B1 but Doe in C1.
Hi Suave,
Welcome to chandoo.org and thanks for commenting.
To get the first name, in B1 write =LEFT(A1,FIND(“.”,A1, IF(SUBSTITUTE(A1,”.”,”",3)=A1,1,FIND(“.”,A1)+1))-1)
To get last name, in C1 write =MID(A1,LEN(B1)+2,FIND(“@”,A1)-LEN(B1)-2)
This assumes that company.com has only one . (ex: company.com works, but not company.com.au needs a fix – change 3 to 4)
Hello Chando,
I’m using your forumula plus a slight modification for proper names but either way I use it, I still get a period before the middle initial:
Firstname.M.Lastname@somewhere.com
=PROPER(LEFT(E2,FIND(“.”,E2,IF(SUBSTITUTE(E2,”.”,”",3)=E2,1,FIND(“.”,E2)+1))-1))
It will output: Firstname.M
I am a novice and I’m not sure what I need to change or adjust to make the period into a space. I would have thought Substitute would have done that.
Hi all,
i have a address list which contains centre name, email ids & telephonic contact in a cell, i want to delete email ids & telephonic contact in a cell..
kindly help me out
Thanks! I’ve been looking all over for this fix.
Hi Chandoo
First of all let me thank you for all the help that I get from your website.
Its really helpfull, specially for beginners like me.
I have one question..how would I extract text from cell with special characters. I know if all the cell has same special character that I can use the above formula but what if one cell contains “.” other contains “:” so here I can’t use the same formula and I don’t want to manually go and change the formula as per the cells requirement in each cell.
Could you please email me the solution?
Thanks a lot.