Can you extract first name & last name from email address? [Formula Challenge]

Posted on February 27th, 2014 in Excel Challenges - 95 comments

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

As you may know, we have an article on how to extract names from email addresses. 2 days ago, Joana commented on it saying,

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

I asked Joana how she did it. And here is the formula she shared (#),

Formula to extract first and last name from email address - shared by Joana

Yes, it is long. It must have taken a lot of concentration, ninja-level skills to come up with this.
(Note: The formula is in Portuguese or Spanish version of Excel. So do not try it in English version)

Here is your challenge.

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Things to keep in mind:

  • Assume only English alphabet in names. That means no letters like áèó etc.
  • The email address contains only firstname_separator_lastname. No middle name or other prefix or suffix etc.
  • The email address is in A1
  • Assume B1 contains just the name portion of email (ie john_doe in B1 if A1 contains john_doe@email.com)
  • In C1 & D1 you need to extract first name & last name.

Example email addresses:

  • john_doe@email.com -> john doe
  • john.doe@email.com -> john doe
  • john123doe@email.com -> john doe
  • john-doe@email.com -> john doe
  • john1964doe@email.com -> john doe

Sample file

Download the sample file containing email addresses and expected results. Use it to write your formulas.

How to post your answers?

Simple. Just comment on this post with your answers. Tell us how you arrived at the formula, what it does. It will help rest of us understand and use your formulas.

Special note: If your formula contains < or > symbols when posting it, use &lt; and &gt; instead. Our commenting system eats up < and > symbols.

Go ahead and liberate John Doe from john4doe@email.com. We are waiting…

Want more Excel challenges?

Try this – more than 25 challenges and problems in Excel.

Your email address is safe with us. Our policies

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

95 Responses to “Can you extract first name & last name from email address? [Formula Challenge]”

  1. JLeno says:

    Okay, first try:

    First name:
    =LEFT(C4,MIN(IF((CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1))91)*(CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1))122),ROW(INDIRECT(“1:”&LEN(C4)))))-1)

    Last name:
    =RIGHT(C4,LEN(C4)-MAX(IF((CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1))91)*(CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1))122),ROW(INDIRECT(“1:”&LEN(C4))))))

    (array entered!)

    Key is the CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1)) part, which does the following:

    MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1) splits the text to 1 character string texts
    CODE(x) converts all text strings to ASCII codes

    The logical test checks all ASCII codes, if they are not between 65 and 90 or between 97 and 122 then it is a nontext character.

    The formula uses the first and last nontext character position in the LEFT and RIGHT statements.

    I’m not sure if there are any optimisations possible, or perhaps even a totally different approach?

    Nice challenge by the way :)

  2. on the fly …

    B1=LEFT(A1,FIND(“@”,A1)-1)

    C1=LEFT(B1,MIN(IF(ISERROR(FIND(MID(B1,ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99)))-1)

    D1=RIGHT(B1,LEN(B1)-MAX(IF(ISERROR(FIND(MID(B1&REPT(“a”,100),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99))))

    regards
    r

  3. ops … find is case-sensitive … replace with SEARCH

    C1=LEFT(B1,MIN(IF(ISERROR(SEARCH(MID(B1,ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99)))-1)

    D1=RIGHT(B1,LEN(B1)-MAX(IF(ISERROR(SEARCH(MID(B1&REPT(“a”,100),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99))))

  4. shorten the formula in D1
    =MID(B1,MAX(IF(ISERROR(SEARCH(MID(B1&REPT(“a”,99),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99)))+1,99)

  5. sam says:

    First Name
    =MID(C4,1,MIN(IFERROR(FIND(CHAR(ROW($1:$95)),C4),FALSE))-1)
    Last Name
    =MID(C4,MAX(IFERROR(FIND(CHAR(ROW($1:$95)),C4),FALSE))+1,LEN(C4))
    C4 – having the formula
    =LOWER(LEFT(B4,FIND(“@”,B4)-1))

  6. sam says:

    Sorry the Last Name needs to be changed

  7. Sam Mathai Chacko says:

    Note sure if this was open to everybody, so just trying my luck. I would have used followed suit and used
    B1=LEFT(A1,FIND(“@”,A1)-1), but just for posterity, I’ll use the one below

    B1=REPLACE(A1,FIND(“@”,A1),255,””)

    C1=LEFT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),ROW(OFFSET(A$1,,,LEN(B1))),1))90),)-1) {array}

    D1=RIGHT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),LEN(B1&” “)-ROW(OFFSET(A$1,,,LEN(B1))),1))90),)-1){array}

  8. Sam Mathai Chacko says:

    Something’s wrong with the posting Chandoo. Some text is being replaced / truncated. My solution is only showing half the formula!!

  9. Stéphane says:

    Hi

    I made this exercise for Google Spreadsheet
    =split(index(split(A1;”@”);1;1);REGEXEXTRACT(index(split(A1;”@”);1;1);”[0-9_.-]+”))

    For french reader : http://justdocsit.blogspot.com/2014/02/extraire-le-prenom-et-le-nom-dun-email.html

    Stéphane

  10. Sam Mathai Chacko says:

    I’ll try again. Hope this one works.

    ‘=LEFT(A1,FIND(“@”,A1)-1)

    ‘=LEFT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),ROW(OFFSET(A$1,,,LEN(B1))),1))<90),)-1)

    ‘=RIGHT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),LEN(B1&” “)-ROW(OFFSET(A$1,,,LEN(B1))),1))<90),)-1)

  11. Sam Mathai Chacko says:

    Nope! something wrong. text getting removed…. probably has something to do with the +, >, < characters… not sure

  12. SHUSHU says:

    Sam,
    Try to take a snapshot from your worksheet where you will show the formulas as text and/or comments.
    Upload the picture to some file hosting site and present’ us’ the link.
    SHUSHU

  13. Michael (Micky) Avidan says:

    @Sam,
    Take a snapshot of your sheet after presenting the formula(s) as TEXT + put the formulas into their cell comments.
    Then, upload the picture to a file hosting site and return to present the link to that picture
    Michael

  14. as Joana’s formula this one return first & last name separated by a space … however it is a bit shorter :-)
    mail address in A1:
    =REPLACE(LEFT(A1,FIND(“@”,A1)-1),MIN(IF(ISERROR(SEARCH(MID(A1,ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”)),ROW($1:$99))),SUM(–ISERROR(SEARCH(MID(LEFT(A1,FIND(“@”,A1)-1),ROW($1:$99),1),”qwertyuiopasdfghjklzxcvbnm”))),” “)

  15. Sam Mathai Chacko says:

    OK, trying Hui’s suggestion

    =LEFT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),ROW(OFFSET(A$1,,,LEN(B1))),1)) 90),)-1)

    =RIGHT(UPPER(B1),MATCH(1,(CODE(MID(UPPER(B1),LEN(B1&” “)-ROW(OFFSET(A$1,,,LEN(B1))),1)) 90),)-1)

  16. XLarium says:

    Hi

    My humble contribution.

    In C1:
    {=LEFT(B1,MATCH(0,COUNTIF($Z$1:$Z$26,MID(B1,COLUMN(1:1),1)),0)-1)}

    In D1:
    {=MID(B1,LEN(C1)+MATCH(1,COUNTIF($Z$1:$Z$26,MID(SUBSTITUTE(B1,C1,””),COLUMN(1:1),1)),0),999)}

    Z1:Z26 house the valid characters A..Z.

  17. Dan says:

    =LEFT(B1,SEARCH(“_”,B1)-1)
    =RIGHT(B1,LEN(B1)-SEARCH(“_”,B1,1))

  18. Luke M says:

    Looks like others may have come up with similar:
    =LEFT(C4,MIN(IF(((CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))&lt97)+(CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))&gt122)),ROW(INDIRECT(“1:”&LEN(C4)))))-1)

    =MID(C4,MAX(IF(((CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))&lt97)+(CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))&gt122)),ROW(INDIRECT(“1:”&LEN(C4)))))+1,999)

    • Luke M says:

      Bah, missed the semicolon. And to clarify, these are array formulas:
      =LEFT(C4,MIN(IF(((CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))<97)+(CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))>122)),ROW(INDIRECT(“1:”&LEN(C4)))))-1)

      =MID(C4,MAX(IF(((CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))<97)+(CODE(MID(LOWER(C4),ROW(INDIRECT(“1:”&LEN(C4))),1))>122)),ROW(INDIRECT(“1:”&LEN(C4)))))+1,999)

  19. Daniel Ferry says:

    First Name:

    {=LEFT(C4,MATCH(1,–(CODE(MID(C4,ROW($1:$99),1)) lt; 96),)-1)}

    Last Name:

    =MID(C5,LOOKUP(1,1/(CODE(MID(C5,ROW($1:$99),1)) lt; 96),ROW($1:$99))+1,99)

  20. Daniel Ferry says:

    First Name:

    {=LEFT(C4,MATCH(1,–(CODE(MID(C4,ROW($1:$99),1)) <96),)-1)}

    Last Name:

    =MID(C5,LOOKUP(1,1/(CODE(MID(C4,ROW($1:$99),1)) <96),ROW($1:$99))+1,99)

  21. Daniel Ferry says:

    OK. Last attempt to get past the comment parser…

    First Name:

    {=LEFT(C4,MATCH(1,- -(CODE(MID(C4,ROW($1:$99),1)) <96),)-1)}

    Last Name:

    =MID(C4,LOOKUP(1,1/(CODE(MID(C4,ROW($1:$99),1)) <96),ROW($1:$99))+1,99)

  22. Mike Daniels says:

    I did them separately:
    john_doe@eamile.com: =CONCATENATE(LEFT(B2,4),” “,(MID(B2,6,3)))
    john.doe@email.com: =CONCATENATE(LEFT(B3,4),” “,(MID(B3,6,3)))
    Hope you get the picture.
    Fun to do – thanks.

  23. MF says:

    D4:
    =LEFT(C4,MATCH(TRUE,LOOKUP(CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1)),{0;65;91;97;123},{“nontext”;”text”;”nontext”;”text”;”nontext”})=”nontext”,0)-1)
    CTRL SHIFT ENTER

    E4:
    =RIGHT(C4,LEN(C4)-MAX(IF(LOOKUP(CODE(MID(C4,ROW(INDIRECT(“1:”&LEN(C4))),1)),{0;65;91;97;123},{“nontext”;”text”;”nontext”;”text”;”nontext”})=”nontext”,ROW(INDIRECT(“1:”&LEN(C4))))))
    CTRL SHIFT ENTER

  24. Sam Mathai Chacko says:

    One more attempt to escape from the parser

    `C1=LEFT(B6,MATCH(1,–(ISNA(MATCH(CODE(MID(UPPER(B6),ROW($1:$99),1)),ROW($65:$90),))),)-1)

    `D1=RIGHT(B6,MATCH(1,–(ISNA(MATCH(CODE(MID(UPPER(B6),LEN(B6&0)-ROW(OFFSET(A$1,,,LEN(B6))),1)),ROW($65:$90),))),)-1)

  25. Istiyak Shaikh says:

    Is there any solution for below

    Johndoe@gmail.com

    Regards,
    Istiyak

  26. Mike Daniels says:

    Hello Everyone,
    While I’m impressd with some of the answers given, it is always my preference to keep things simple and “elegant”. It is unlikely that I would have to work with a data set containing so many different iterations of an email address; more likely one or two. I humbly submit my answers below. They are constructed from my Chandoo Excel Lessons, which are helping me become AWSOME in Excel. Thank you.

    john_doe@email.com =CONCATENATE(LEFT(B2,4),” “,MID(B2,6,3))
    john.doe@email.com =CONCATENATE(LEFT(B3,4),” “,MID(B3,6,3))
    john123doe@email.com =CONCATENATE(LEFT(B4,4),” “,MID(B4,8,3))
    john-doe@email.com =CONCATENATE(LEFT(B5,4),” “,MID(B5,6,3))
    john1964doe@email.com CONCATENATE(LEFT(B6,4),” “,MID(B6,9,3))

    • Daniel Ferry says:

      Hi Mike.

      It is with respect that I mention this. Please try to accept challenges such as this one as an opportunity to learn by pushing your understanding.

      Elegant solutions are concise but at the same time work over a generalized problem domain. Perhaps you will never have such a dataset to work on, but the solutions shown here work in an infinite number of scenarios that have nothing to do with email addresses.

      Hardcoding splice points to a string is anything but generalized as your small sampling of formulas demonstrate. It is extremely unlikely that all of your addresses have John for the first name, or even a four-letter first name.

      The challenge here is to create ONE formula that will work with first and last names of any length and that ONE formula can simply be copied down a column adjacent to the addresses. This is actually pretty simple, but this particular challenge is a little more interesting as the type of non-alpha characters and the number of them used within any give address is unknown, so the formula must manage that as well.

      Again, the tactics used to solve this can be used in an infinite number of Excel scenarios that have nothing to do with email addresses.

      You will become exponentially more productive when you learn how to wield advanced Excel techniques.

      .

  27. steven portman says:

    I looked around and found a solution for this somewhere.

    I tried this;

    First name: =MID(A1,FIND(“john”,A1),LEN(A1)-FIND(“l.com”,A1))
    Last name: =MID(A1,FIND(“doe”,A1),LEN(A1)-FIND(“.com”,A1))

    Seems to work. Don’t know why though (I’m a newbie).
    Just need to remember the “FIND” is case sensitive.

  28. Sam Mathai Chacko says:

    Can someone tell me why nobody is considering the characters {, |, }

  29. Well I never though my formula would cause such an impact…lol

    I have to say I’m not an Excel expert, very far from it and I see that there are already much better ways to do what I needed to do, which was to locale and extract names from emails.

    Let me just say that this came up when a company I work for needed to email their 20k email list and I suggested they would personalize each email. Guess who got to extract the names. I said to my boss, well this will take me all week, but, 2 hours latter it was done :) (not getting a raise though…lol)

    Glad to see so much people involved in this, keep it up :)

  30. Aaron says:

    The code below is a bit long, but it creates a full index array of all available text characters to compare vs. the non text characters. Then it uses the smallest indexed to extract the first name, and the 2nd and 3rd kth largest to extract the last name. Works well for all variants of the email addresses given.

    LEFT(B10;MIN(IF((N(ISERROR(MATCH(CODE(MID(B10;ROW(OFFSET($A$1;;;LEN(B10)));1));IF(ROW(OFFSET($A$1;64;;52))>90;0;ROW(OFFSET($A$1;64;;52)))+IF(ROW(OFFSET($A$1;70;;52))0;N(ISERROR(MATCH(CODE(MID(B10;ROW(OFFSET($A$1;;;LEN(B10)));1));IF(ROW(OFFSET($A$1;64;;52))>90;0;ROW(OFFSET($A$1;64;;52)))+IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+IF(ROW(OFFSET($A$1;70;;52))<97;0;ROW(OFFSET($A$1;70;;52)));0)))*ROW(OFFSET($A$1;;;LEN(B10)));3)+1))

    • Aaron says:

      I tried to break the monster formula below due to the missing parts above. Learning how to post here :). When you see it broken down below, you can see the main context of the formula repeats. I just use SMALL and LARGE to find the indexes for the LEFT and MID formulas.

      EXTRACT THE FIRST NAME USING THE LEFT FORMULA

      =LEFT(B4;

      FIND THE MINIMUM INDEXED NON CHARACTER

      MIN(IF((N(ISERROR(MATCH(CODE(MID(B4;ROW(OFFSET($A$1;;;LEN(B4)));1));

      IF(ROW(OFFSET($A$1;64;;52))>90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))0;

      N(ISERROR(MATCH(CODE(MID(B4;ROW(OFFSET($A$1;;;LEN(B4)));1));

      IF(ROW(OFFSET($A$1;64;;52))>90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))<97;0;ROW(OFFSET($A$1;70;;52)));0)))
      *ROW(OFFSET($A$1;;;LEN(B4)));3)+1))

  31. Aaron says:

    Ok, i can not post my glorious monster formula, so I will just add a link to the workbook in dropbox

    https://dl.dropboxusercontent.com/u/2950543/extract-name-from-email-challenge-Aaron.xlsx

    • Aaron says:

      1) Extract all string characters to array (range X)
      MID(B4;ROW(OFFSET($A$1;;;LEN(B4)));1)

      2) convert all array characters to unicode
      CODE(X)

      3) Create array of unicode text for comparison
      ROW(OFFSET($A$1;64;;52))
      ROW(OFFSET($A$1;70;;52))

      4) Use IF to remove unicode out of range and combine arrays with addition (range Y)
      IF(ROW(OFFSET($A$1;64;;52))>90;0;ROW(OFFSET($A$1;64;;52)))+
      IF(ROW(OFFSET($A$1;70;;52))0; Z))-1)

      7) For the last name, use the same procedure but remove the right text from the string after “@”.
      LEFT(B4;SEARCH(“@”;B4)-1);

      8) Find the number of characters in the right side of the string by subtracting the MAX non-text indexed position from the length of the string. Replace all string length references with the adjustment in step 7.
      RIGHT(“Step 7″; LEN(“Step 7″) – MAX(IF(Z>0;Z)))

  32. XOR LX says:

    =LEFT(A1,MATCH(TRUE,INDEX(ABS(110-CODE(MID(A1,ROW($1:$99),1)))>13.5,,),0)-1)

    Regards

  33. XOR LX says:

    Correction, for symmetry:

    =LEFT(A1,MATCH(TRUE,INDEX(ABS(109.5-CODE(MID(A1,ROW($1:$99),1)))>12.5,,),0)-1)

    Regards

  34. ilana says:

    Sorry. but I think that you are working too hard. Save it to text file, replace separator between first name and last name with @, open the file from Excel with Delimited option and @ as separator.

  35. Jeanbar says:

    To follow Stéphane’s example on regular expressions (but using Excel):

    First name : ExtractSentence(RC3;”^([a-z]*).*$”)

    Last name : =ExtractSentence(RC3;”^[a-z]*[^a-z]*([a-z)]*).*$”)

    The ExtractSentence function just activates the Microsoft VBScript Regular Expressions Reference 5.5.

    The first argument is the reference of the data
    The second argument is the “pattern” identifying the substring (in parentheses) within a string of characters.

    It can be read like :

    “First Name: from the beginning, fetch any consecutive alphabetical character [a-z] and forget the remaining characters starting with a non-alphabetical character”;

    “Last Name : from the beginning, skip any consecutive alphabetical character followed by any consecutive non-alphabetical character, then fetch any consecutive alphabetical character (the name) and skip the remaining characters (starting with the non-alphabetical character @)”.

    Link: https://drive.google.com/file/d/0ByQ7-BavxWcNYVNaSmNKdWNISmc/edit?usp=sharing

  36. Tom Cairns says:

    A bit longwinded, but if column C is
    =LOWER(LEFT(B2, FIND(“@”, B2)-1)
    and column b is the email addresses
    then I made an 80 character equivalent of the “name” part in column d by using
    =REPT(“_”, 80-len(c2))&c2

    in column e I calculate the number of non-alpha characters
    {=SUM(–(CODE(MID(D24,ROW($A$1:$A$80),1)) <97))}

    And in column F I find the last non-alpha character

    {=MAX(–(CODE(MID(d23,ROW($A$1:$A$80),1)) <97)*ROW($A$1:$A$80))}

    Then the last name in column H is just
    =right(d2, 80-f2)

    And the first name in column G is just
    =left(c2, f2-e2)

  37. Mark Duchesne says:

    This was a tricky one and had me stumped for days.

    This will extract the first name. Havnt even tried to work out the second name yet….

    B6 = cell with the email
    Formula is entered as an array

    Originally i was missing the parenthesis for the individual expressions causing excel to evaluate an unintended result. After adding the brackets it worked!

    =LEFT(B6,MIN(IF((CODE(MID(B6,ROW(INDIRECT(“1:”&LEN(B6))),1))122),ROW(INDIRECT(“1:”&LEN(B6))),””))-1)

    I love it how everyone thinks so differently, great to see so many different ways of arriving at the same result.

  38. Mark Duchesne says:

    Here was my working to extract the second name, not pretty but it works :)

    =RIGHT(LEFT(B10,SEARCH(“@”,B10)-1),(SEARCH(“@”,B10)-1)-MAX(IF((CODE(MID(B10,ROW(INDIRECT(“1:”&(SEARCH(“@”,B10)-1))),1))122),ROW(INDIRECT(“1:”&(SEARCH(“@”,B10)-1))),””)))

  39. Would prefer using “Text-to-column” approach.

    Ex : John.doe@email.com
    1st Step in delimiters , use “@” :
    Hence, you would get John.Doe and email.com as separate

    2nd Step for first name / last name , use “.” as a delimiter
    Hence, You would get John and Doe as separate.

    Instant results.

  40. […] site do chandoo foi postado um desafio de tratamento de dados bem complicado de se […]

  41. Athar Siddiqui says:

    So we start with fred.bloggs@stink.com

    if the cell is F5 then fred is:

    =LEFT(F5,FIND(“.”,F5)-1) and

    bloggs is:

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

    a colleague showed me this and it beats everything above

  42. Athar Siddiqui says:

    (I cant claim credit)

    • Mark duchesne says:

      Hi Atar,

      Your formula only works where there is a single “.” In the email address. What if the email have 2 dots or 3 dots or any other combination of non text characters? Try you formula on the test examples given and you will see that it doesn’t work.

      Keep trying;)

      Mark

  43. Chris says:

    you could just use extractmails.com to process complex scenarios, it’s faster.

  44. AlexK says:

    This should work with iterative calculations turned on and will work with capitalisation and any character you can throw at it I believe.

    Assumes email is in cell A1.

    First name in cell C1:
    =IFERROR(IF(C1<100,IF(OR(AND(CODE(MID($A1,C1,1))>=97,CODE(MID($A1,C1,1))<=122),AND(CODE(MID($A1,C1,1))>=65,CODE(MID($A1,C1,1))<=90)),C1+1,LEFT($A1,C1-1)),C1+1),1)

    Last name in cell D1:
    =IFERROR(IF(D1<100,IF(OR(AND(CODE(MID($A1,FIND(“@”,$A1)-D1,1))>=97,CODE(MID($A1,FIND(“@”,$A1)-D1,1))<=122),AND(CODE(MID($A1,FIND(“@”,$A1)-D1,1))>=65,CODE(MID($A1,FIND(“@”,$A1)-D1,1))<=90)),D1+1,MID($A1,FIND(“@”,$A1)-D1+1,D1-1)),D1+1),1)

    Apologies if this has already been posted, I wanted to work it out before checking the other submissions.

    • Rudra Sharma says:

      If I place your formulas in cells you specified it shows error (Circular Reference error).
      If I place them to someother cell then they just give result as 1.

      With Regards
      Rudra

      • AlexK says:

        Hi Rudra,

        If you go into Options then formulas and then tick ‘Enable iterative calculation’. Once you have done this, paste the formula back in and it should work. I have also noticed that you have to change the quotation marks from “ & ” to ” to make it work.

  45. Socrates G says:

    the formula I used was:

    =left(cell,find(“@”,cell)-1) – seems to work, but I have a bit of a block retrieving the domain name

  46. MOHA says:

    =LEFT(B4;FIND(“_”;B4)-1)=Jhon
    =RIGHT(LEFT(B4;FIND(“@”;B4)-1);3)=doe

  47. DavidRaj says:

    =MID(B7,1,4) = John
    =RIGHT(LEFT(B7,(FIND(“@”,B7)-1)),3) = doe

  48. SAMBIT KUMAR MOHAPATRA says:

    =LEFT(D4,4) = John
    =RIGHT(LEFT(D4,FIND(“@”,D4)-1),3) = doe

  49. ARVIND says:

    =LEFT(cell,FIND(“@”,cell)-1)

  50. Naveen BP says:

    =LEFT(D23,4) for first name
    =RIGHT(LEFT(D23,(FIND(“@”,D23))-1),3) to find last name

  51. Abbott Katz says:

    The LEFT formulation works only if you assume that the first name in question is always John, as per the actual examples. But if you want to prepare an expression that could glean ANY first name the problem gets considerably trickier.

  52. Shaikh Sajid says:

    1)=LEFT(A1,FIND(“@”,A1,1)-1)
    2)=LEFT(D1,FIND(“_”,D1,1)-1)
    3)=RIGHT(D1,LEN(D1)-FIND(“_”,D1,1))

  53. Peter says:

    Some great formulas, especially liking Roberto’s version, which I amended to be able to find the last name from the whole email (instead of having to split out first/last names initially)

    =MID(A3,FIND(RIGHT(B3,MATCH(1=1,MID(B3,LEN(B3)-ROW($1:$99),1)<"a",)),B3),(FIND("@",A3)-FIND(RIGHT(B3,MATCH(1=1,MID(B3,LEN(B3)-ROW($1:$99),1)<"a",)),B3)))

  54. Amiq Khan says:

    I used the ‘Text to Columns’ functionality. First separate text on the basis of @ and then _ and we will have just the first name and last name in two columns.

  55. i used this for the question.

    For Name portion
    =LEFT(D31,SEARCH(“@”,D31)-1)

    For First Name
    =LEFT(LEFT(D31,SEARCH(“@”,D31)-1),4)

    For Last Name
    =RIGHT(LEFT(D31,SEARCH(“@”,D31)-1),3)

    this formula works only when the names are same, but it will not work in case of different-different names. For that we have to use different formula

  56. HunterHisoka says:

    C1 = an email text ( John_2342_4353doe@gmail.com )

    Character (to identify middle)
    A1:A13 fill with ( _ – , . 0 1 2 3 4 5 6 7 8 9 )

    Character (to identify last name)
    B1:B26 fill with ( a b c d e f g h i j k l m n o p q r s t u v w x y z )

    Name of Portion
    D1 : =LEFT(C1;SEARCH(“@”;C1;1)-1)

    First Name
    E1 : =LEFT(D1;MIN(IFERROR(SEARCH(A1:A13;D1;1);”x”))-1) Then Ctrl + Enter

    Last Name
    F1 : =MID(D1;LEN(E1)+MIN(IFERROR(SEARCH(B1:B26;RIGHT(D1;LEN(D1)-LEN(E1));1);”x”));LEN(D1)-LEN(E1)-1) Then Ctrl + Enter

    Best Regard,
    Naruto

  57. Ashok Sindkar says:

    It is very simple with Excel 2013 version. It is having new function Flash fill. Just start typing required output in top cell. Press enter for next cell and start typing … Excel will show all required output in faint letters Just press enter and Done. Try this.
    Thanks.

    • Alexie Nepeh says:

      =LEFT(A2,FIND(“@email.com”,A2,1)-1) to extract the name from the email addresses

      =LEFT(C2,4) to extract the first name

      =RIGHT(C2,3) to extract the last name

      why write complex formular when it can be done easily?

  58. Tom Smith says:

    I went back to the simpler process of breaking things down into steps, after making sure that for valid email addresses the only special characters that are permissable are hyphen (-), underscore (_) , period (.), plus (+) and I added comma (,) and hash (#) just for fun. The steps:
    1. retrive the name portion
    2. replace the characters we don’t want with spaces
    3. trim the results of step 2 and use a Proper function to make them look right (they are names after all)
    4. capture the first and last names from the result of step 3

    Then I put the first 3 steps into a single formula and used a simple parse to capture the names.

    So for the combined steps 1-3, I ended up with:

    =PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(“@”,A1)-1),”.”,” “),”(“,” “),”-”,” “),”_”,” “),”#”,” “),”+”,” “),”1″,” “),”2″,” “),”3″,” “),”4″,” “),”5″,” “),”6″,” “),”7″,” “),”8″,” “),”9″,” “),”0″,” “)))

    Then, with those results in column B, looked for the first name for col C:
    =LEFT(B1,FIND(” “,B1)-1))

    and the last name in col D:
    =MID(B1,FIND(” “,B1)+1,LEN(B1))

    Seems to work for all the combinations mentiond in the requirements.

  59. Sanjeet Singh says:

    LEFT(LEFT(B4,FIND(“@”,$B4,1)-1),4)
    RIGHT(LEFT(B4,FIND(“@”,$B4,1)-1),3)

Leave a Reply