Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]

Posted on January 19th, 2010 in Learn Excel - 62 comments

Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]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!

(syntax and examples)

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:

Extract usernames from E-mail IDs - demo

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:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

62 Responses to “Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]”

  1. Loranga says:

    I’ll just cheat and use text to columns instead ;)

  2. Loranga says:

    I forgot to post my non cheating attemt:
    =RIGHT(B3;LEN(B3)-FIND(“@”;B3;1))

  3. Ubique72 says:

    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

  4. John says:

    =RIGHT(A1,FIND(“@”,A1)-2)

  5. Elias says:

    One more option to extract the domain name.

    =REPLACE(B3,1,FIND(“@”,B3),””)

    Regards

  6. Dale says:

    You might also want to add TRIM to the formula so that embedded get cleaned up.

  7. ericlind says:

    =RIGHT(A1,LEN(A1)-FIND(“@”,A1))

  8. Drew says:

    =RIGHT(A1,FIND(“@”,A1)-1)

    Same as John’s except I don’t think the -2 is necessary vs. -1.

  9. Rick Rothstein (MVP - Excel) says:

    Here is how I would extract the domain name…

    =MID(A1,FIND(“@”,A1)+1,999)

    • Suraj Singh says:

      Rick Rothstein

      Thank you so much your Formula is worked,
      How can i get contact us web pages, Could you please tell me.

      Thanks Again
      Suraj

  10. Rick Rothstein (MVP - Excel) says:

    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.

  11. Scaffdog845 says:

    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.

  12. Elias says:

    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.

  13. Chandoo says:

    @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…

  14. Anurag G says:

    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

  15. Rick Rothstein (MVP - Excel) says:

    @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.

  16. Anurag G says:

    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

  17. Victor says:

    I did just like Loranga.
    Which way is the best?

    Thanks Chandoo!

  18. [...] 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 [...]

  19. RZX says:

    Hi All. I need help to add 1st name, last name with @ domain. Please help.

  20. Rick Rothstein (MVP - Excel) says:

    @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.

  21. Chandoo says:

    @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

  22. dumbee1018 says:

    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?

  23. Chandoo says:

    @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?

  24. Hui... says:

    @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

  25. Niyi Adeyinka says:

    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))”

  26. daya says:

    how to get only last name, when the id is firstname.lastname@domain.com

  27. Vijay Sharma says:

    @Daya

    =TRIM(MID(A1,FIND(“.”,A1,1)+1,FIND(“@”,A1,1)-FIND(“.”,A1)-1))

    ~Vijay

  28. Rahul says:

    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?

  29. Hrishit says:

    Awsum tuts man..it really help me a lot. Work Simplified !!!
    Gr8 going.

  30. Alice says:

    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!

  31. Glen says:

    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).

  32. Suave says:

    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.

    • Chandoo says:

      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)

      • Toxygene says:

        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.

  33. pradeep says:

    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
     

  34. Ca says:

    Thanks!  I’ve been looking all over for this fix.

  35. Azaz says:

    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.

  36. gene says:

    chando,
    How do i separate firstname and last name into b2 and c3 if my data is in A1 as firstname.lastname@mydomain.com

  37. gene says:

    I have a colum of firstnames and last names , i need to make email addresses out of them in the following format , firstname.lastname@mydomain.com Can anyone help me with the best solution to accomplish this

  38. Jason says:

    Brilliant and clear post – saved me hours and thanks from afar! Legend

  39. Nancy says:

    First time poster, here! I hope this makes sense to someone.

    I’m baffled by my latest problem. I’m using almost the exact same formula in this article, but my delimiter is a comma. I’m trying to pull the last name only from a series of cells with lastname, firstname. The formula I’m using is LEFT(A1,(FIND(“,”,A1)-1)).
    The formula works fine for that cell, but when I drag the formula down, the result in ALL cells is the same as the first one! If A1 has Smith, John, A2 has Doe, John, and A3 has Jackson, Don, all three will show Smith in the cells B1-B3!! I checked the formula in each cell and they are correct — meaning the cell number changes to A2, A3 as it’s dragged.

    I restarted my PC and did it again hoping that would help, but got the same results. What’s going on here!?! Can anyone help??

    • Amathya says:

      Hi Nancy,

      It seems your Workbook has Calculation Options set to Manual. Please reset it to Automatic by going to Formulas Tab and then Selecting Automatic Under Calculation Options dropdown.

      Hope this helps.

  40. Joana Pereira says:

    Not to brag but I created a [complex] formula that extracts the names from emails in a much more interesting way. See the examples:

    john_doe@email.com >> my extract >> John Doe
    john.doe@email.com >> my extract >> John Doe
    john321doe@email.com >> my extract >> John Doe

    Clever right? ;)

    I’ll be glad to share.

  41. Ok, here’s my formula:

    SUBSTITUTE(CONCATENATE(IF(ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)=0;””;IF(ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)=1;””;ESQUERDA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”);ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)-1)));” “;IF(ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)=0;””;IF(ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)=1;””;DIREITA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”);NÚM.CARACT(C2)-ISERROR(SEARCH(“-”;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;”.”;”-”);”_”;”-”);”0″;”-”);”9″;”-”);”8″;”-”);”7″;”-”);”6″;”-”);”5″;”-”);”4″;”-”);”3″;”-”);”2″;”-”);”1″;”-”));0)))));”-”;””)

    It may look dauting, but it’s quite simple. All it does is locate “_”,”.” or any numbers and break the string in two.

    Remember you still have to break the email in two strings like in the article, then you apply this formula.

    If you want to go even farther you can capitalize the first letter of the names. :)

    • Chandoo says:

      It is one very long formula. Kudos for coming up with this. I am sure you are a Jedi mind Yoda in previous life.

      • Ron Wallace says:

        Here’s a simplified version of Joana’s technique (in English).

        Given the following address in cell B3:
        alex_1234567890.gomez@email.com

        Use the following formula:
        =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B3,FIND(“@”,B3)-1),”_”,” “),”.”,” “),”1″,” “),”2″,” “),”3″,” “),”4″,” “),”5″,” “),”6″,” “),”7″,” “),”8″,” “),”9″,” “),”0″,” “))

        with the result of alex gomez. From here it’s a simple matter to isolate the first and last names if desired.

        I don’t really like having to use all the SUBSTITUTE()’s and having to specify which characters to replace with spaces. I’m still looking for a way to just replace all character codes except the ranges 65-90 and 97-127 with spaces and then trim without using VBA. Maybe could use an array structure?

      • Most of it was copy and paste into a single formula, but don’t tell anyone :)

  42. […] you may know, we have an article on how to extract names from email addresses. 2 days ago, Joana commented on it […]

  43. Gary says:

    I wouldn’t use a formula, i’d use vba
    Split(Range(“A1″), “@”)(0)

    and to remove the “_”
    Replace(Split(Range(“A1″), “@”)(0), “_”, ” “)

  44. Ron Wallace says:

    This is a redo to fit the formula into your text box.

    Here’s a simplified version of Joana’s technique (in English).

    Given the following address in cell B3:
    alex_1234567890.gomez@email.com

    Use the following formula:
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
    (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
    (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B3,FIND(“@”,B3)-1)
    ,”_”,” “),”.”,” “),”1?,” “),”2?,” “),”3?,” “),”4?,” “),”5?,” “),”6?,” “)
    ,”7?,” “),”8?,” “),”9?,” “),”0?,” “))

    with the result of alex gomez. From here it’s a simple matter to isolate the first and last names if desired.

    I don’t really like having to use all the SUBSTITUTE()’s and having to specify which characters to replace with spaces. I’m still looking for a way to just replace all character codes except the ranges 65-90 and 97-127 with spaces and then trim without using VBA. Maybe could use an array structure?

    • Ron Wallace says:

      PS
      As a trivial addition, if you want the names capitalized, for
      alex_1234567890.gomez@email.com

      just use
      =PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
      (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
      (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT($B9,FIND(“@”,$B9)-1)
      ,”_”,” “),”.”,” “),”1″,” “),”2″,” “),”3″,” “),”4″,” “),”5″,” “),”6″,” “),”7″,” “),”8″,” “),”9″,” “),”0″,” “)))

  45. subburaj says:

    =RIGHT(A3,LEN(A3)-FIND(“@”,A3))

  46. Avnesh Chaudhary says:

    plz tell me how to split first and last name if they are combined with any character other than letters. E.G.Avnesh.chaudhary
    vishal1mittal
    akash#sharma
    Prateek_bansal
    plz help me..plz

  47. Steve says:

    This works nicely for me. Thanks for all the input that led me to this point.

    It takes an email address in the firstname.lastname@domain.com format and returns it as Firstname Lastname

    =PROPER(TRIM((SUBSTITUTE(LEFT(A1,FIND(“@”,A1)-1),”.”,” “))))

    If you need to remove other characters, just add additional SUBSTITUTE conditions

  48. Paul says:

    I have to tell you, because it was driving me crazy, “eg” is latin for exempli gratia, which mean “for the sake of example” or “for example”. There is no need to put “for eg” because that’s like saying “for for example”.

Leave a Reply