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

Excel Challenges - 131 comments

Today lets rescue John Doe from

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: >> my extract >> John Doe >> my extract >> John Doe >> 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

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
  • In C1 & D1 you need to extract first name & last name.

Example email addresses:

  • -> john doe
  • -> john doe
  • -> john doe
  • -> john doe
  • -> 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 We are waiting…

Want more Excel challenges?

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


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

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

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

  1. JLeno says:

    Okay, first try:

    First name:

    Last name:

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





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



  4. shorten the formula in D1

  5. sam says:

    First Name
    Last Name
    C4 - having the formula

  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


    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:


    I made this exercise for Google Spreadsheet

    For french reader :


  10. Sam Mathai Chacko says:

    I'll try again. Hope this one works.



    '=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:

    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.

  13. Michael (Micky) Avidan says:

    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

  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:


    My humble contribution.

    In C1:

    In D1:

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

  17. Dan says:


  18. Luke M says:

    Looks like others may have come up with similar:


    • Luke M says:

      Bah, missed the semicolon. And to clarify, these are array formulas:


  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: =CONCATENATE(LEFT(B2,4)," ",(MID(B2,6,3))) =CONCATENATE(LEFT(B3,4)," ",(MID(B3,6,3)))
    Hope you get the picture.
    Fun to do - thanks.

  23. MF says:



  24. Sam Mathai Chacko says:

    One more attempt to escape from the parser



  25. Istiyak Shaikh says:

    Is there any solution for below


  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. =CONCATENATE(LEFT(B2,4)," ",MID(B2,6,3)) =CONCATENATE(LEFT(B3,4)," ",MID(B3,6,3)) =CONCATENATE(LEFT(B4,4)," ",MID(B4,8,3)) =CONCATENATE(LEFT(B5,4)," ",MID(B5,6,3)) 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("",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

    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

    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.


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








  31. Aaron says:

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

    • Aaron says:

      1) Extract all string characters to array (range X)

      2) convert all array characters to unicode

      3) Create array of unicode text for comparison

      4) Use IF to remove unicode out of range and combine arrays with addition (range Y)
      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 "@".

      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:



  33. XOR LX says:

    Correction, for symmetry:



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


  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!


    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 🙂


  39. Would prefer using "Text-to-column" approach.

    Ex :
    1st Step in delimiters , use "@" :
    Hence, you would get John.Doe and 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

    if the cell is F5 then fred is:

    =LEFT(F5,FIND(".",F5)-1) and

    bloggs is:


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


  43. Chris says:

    you could just use 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:

    Last name in cell D1:

    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

      • 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:


  47. DavidRaj says:

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


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

  49. ARVIND says:


  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:


  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)


  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

    For First Name

    For Last Name

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

    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,

  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.

  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:


  60. Lynn C says:

    Could you just:
    Search the list for the rarest # in the emails addys (say it's #8)
    Cut & paste those few addys to a new column
    Original data, replace @ with 8
    Use 8 as separating value

    Manually separate the few in the new column

  61. suyash says:

    We can use Left and right formula
    for ex.

    john999doe to extract John "=left(john999doe,4) as from left only 4 characters we need"
    and for
    "=right(john999doe,3) as from right only 3 characters we need"

  62. karl says:


  63. Peter Thompson says:

    I decided to make a function to do this:

    The code is below this works by entering =NameFromEmail in a cell with the cell and then either 1 or 2 as parameter. So in the example worksheet the email address is in column b so if you enter in cell d4

    =NameFromEmail(b4,1) then it will return the first name and if in cell e4 you enter =NameFromEmail(b4,2) it will return the 2nd name.

    Function NameFromEmail(ByVal rngEmail As Range, ByVal lngFirstOrLastName)

    Dim strEmail As String
    strEmail = rngEmail.Value

    Dim lngStringLength As Long
    lngStringLength = Len(strEmail)

    'Loop through string
    For I = 1 To lngStringLength

    Dim lngAscNumber As Long, lngCountNonChar As Long, lngEndOfFirstName As Long, lngEndOfName As Long

    'Get the Ascii number of the email string
    lngAscNumber = Asc(Mid(strEmail, I, 1))

    'Check for non text symbol
    If lngAscNumber < 97 Then '''Note there should be at LT symbol in case it doesn't show on the websit
    'look for @ symbol
    If lngAscNumber = 64 Then
    lngEndOfName = I
    'If the @ symbol has been encountered increment I s we quit the loop
    I = lngStringLength

    If lngCountNonChar = o Then
    lngEndOfFirstName = I
    End If

    'Count non consequitive letters for use later on in the string formulas
    lngCountNonChar = lngCountNonChar + 1
    End If
    End If

    Next I

    Dim strName As String

    'identify what name is required and get appropriatte string
    If lngFirstOrLastName = 1 Then
    strName = Left(strEmail, lngEndOfFirstName - 1)
    strName = Mid(strEmail, lngEndOfFirstName + lngCountNonChar, lngEndOfName - (lngEndOfFirstName + lngCountNonChar))
    End If

    NameFromEmail = strName

    End Function

  64. Ashok says:

    I tried following and got output, but I am not satisfied. I have taken output of first formula in a C column then used second formula to get final output in column D.
    Friends, I am sure you will guide me, how to get output of first formula in to second without using C column.
    Thanks in advance.

    =CONCATENATE(LEFT(B3,4)," ",RIGHT(B3,13))
    Write these formulas and drag. john john doe john john doe john john doe john john doe john john doe john john doe john john doe

  65. Danail says:

    Hi there,

    I will break my answer in its parts for easier explanation. The assume the emails are positioned on the sheet as in the sample file I downloaded here where the name portion are in col C4:C10.

    First, I get the position of the first non-alphabetic character in the name before the by (in cell D4):
    The number 96 is the the ASCII code of "a" minus 1.

    I convert the string in an array of ASCII symbols and use simple MATCH on it.
    Then it is extremely easy to extract the first name (in cell E4):

    The I find the last occurrence of a non-alpha character in the string that results from the original name portion stripped with the first name (in cell F4):
    The second MID function strips the full name part from the the fist name. The other part is same as with extraction of first name with only difference of the last parameter for MATCH to be -1 as I am looking for the first occurrence of alpa-character.

    The last name then is again easy (cell G4):

    I could then put everything together in two scary formulas:
    First Name (in H4):
    Last Name (I4) (extremely scary):

    How do you find my approach?


  66. LEE THANG YEN says:

    =LEFT(A5,4)&" "&MID(A5,SEARCH("@",A5)-3,3)
    I think mine is super formula, can apply use for all the emails.

  67. Daniel Ferry says:

    @Lee Thang Yen,

    What happens with your formula when the first name is not exactly four characters long, or when the last name is not exactly three characters long?

  68. Daniel Ferry says:

    @Peter Thompson

    Working with byte arrays in more efficient than working with strings.

    The following UDF should be array-entered over two adjacent cells, the first cell will display the First Name and the second will display the Last Name:

    Function EmailNames(r As Range) As Variant

    Dim b() As Byte
    Dim vOut(0, 1) As Variant
    Dim i As Long, p1 As Long, p2 As Long, p3 As Long

    b = LCase$(r)
    For i = 0 To UBound(b) Step 2
    If b(i) > 96 And b(i) < 123 Then
    If p1 And p2 = 0 Then p2 = i + 1
    If p1 = 0 Then p1 = i - 1
    If p2 Then p3 = i - 1: Exit For
    End If
    vOut(0, 0) = LeftB(b, p1): vOut(0, 1) = MidB(b, p2, p3 - p2 + 1)

    EmailNames = vOut

    End Function

    • Peter Thompson says:


      Thanks for the feedback.

      When I try and run this code it gives errors around the second group of if statements. Also the vOut statement at the end errors.

      Can I also ask why the step 2 is used I understand what it does, but I would like to understand why it is used in this context.


      • Daniel Ferry says:


        Hmm. Are you in a locale other than US English?

        The line that assigns the range text (r) to the byte array (b) converts VBA's BSTR representation of the string to the byte array. This creates a byte array with two bytes per character in the text string.

        This is why we step by two. The array is base ZERO so the character code values that we are interested in are at the byte array indexes of 0, 2, 6, 8, 10, etc.

        However, if you are in a locale that uses both bytes to represent your characters, then this WILL NOT WORK.

        • Peter Thompson says:


          My PC is on English (United Kingdom) would this stop it from working?



          • Daniel Ferry says:


            No, it would not.

            But I found the problem. I just noticed that when a reader copies my UDF from this blog post the minus signs get translated into another character that looks like a minus sign, but is not.

            Specifically, the minus signs in my code should be ASCII character number 45, but this blog post is displaying them as ASCII character 150.

            So, when you paste my UDF into the VBE in Excel, simply delete the three minus signs and replace them by typing them from your keyboard.

            That should do it.

            Please remember that the UDF returns an array of one cell high and two cells wide. This means it should be array-entered over two adjacent cells and confirmed simultaneously for those two cells.


  69. Giancarlo says:

    Using the formulas GetFirst & GetLast

    Option Explicit

    Function GetFirst(r As String) As String
    Dim arr As Variant
    With CreateObject("vbscript.regexp")
    .Pattern = "[_]{1,8}"
    If .Test(r) Then GetFirst = .Execute(r)(0)
    .Pattern = "[.]"
    If .Test(r) Then GetFirst = GetFirst & .Execute(r)(0)
    .Pattern = "[0-9]{1,8}"
    If .Test(r) Then GetFirst = GetFirst & .Execute(r)(0)
    .Pattern = "[-]"
    If .Test(r) Then GetFirst = GetFirst & .Execute(r)(0)
    End With
    arr = Split(r, GetFirst)
    GetFirst = arr(0)
    End Function

    Function GetLast(r As String) As String
    Dim arr As Variant
    With CreateObject("vbscript.regexp")
    .Pattern = "[_]{1,8}"
    If .Test(r) Then GetLast = .Execute(r)(0)
    .Pattern = "[.]"
    If .Test(r) Then GetLast = GetLast & .Execute(r)(0)
    .Pattern = "[0-9]{1,8}"
    If .Test(r) Then GetLast = GetLast & .Execute(r)(0)
    .Pattern = "[-]"
    If .Test(r) Then GetLast = GetLast & .Execute(r)(0)
    End With
    arr = Split(r, GetLast)
    GetLast = arr(1)
    End Function

  70. Anwar Alrubeidy says:

    Somewhat time consuming formulas, where i have done a separate
    formulas in each row,sent file by email.

  71. Anwar Alrubeidy says:

    Email name Portion =LEFT(A2,FIND("@",A2)-1) =LEFT(A3,FIND("@",A3)-1) =LEFT(A4,FIND("@",A4)-1) =LEFT(A5,FIND("@",A5)-1) =LEFT(A6,FIND("@",A6)-1) =LEFT(A27,FIND("@",A7)-1) =LEFT(A8,FIND("@",A8)-1)
    First Name
    =SUBSTITUTE(LEFT(B4,SEARCH("321",B4)-1),321,"",1) etc etc
    Last Name
    =SUBSTITUTE(RIGHT(B4,SEARCH("321",B4)-2),"321","",1) etc etc
    hope i answered the question

  72. Candy says:

    Formula: =Left(B4,4)&" "&Left(Right(B4,13),3)

    This formula will apply to all rows giving you same answer = john doe

  73. Maloo says:

    What does match(1=1,....) signifies.

    I mean 1=1, means?

    • Daniel Ferry says:


      1=1 means TRUE.
      1=0 means FALSE.

      This convention is often employed by formula crafters that try to make formulas as concise as possible, when suggesting a solution to a formula challenge.

      The convention saves a couple of characters in formula length.

  74. Mehmet Gunal OLCER says:

    I think there happened a problem while I was sending my respond so it is not published.

    In my solution only the columns D & E are used. Column D is the output for First Names where the first letter starts with capital while the Column E is output for Last Names. Last name is completely written in uppercase. Column B contains the e-mail address.

    The formula in the cell in Column D is as follows:

    =PROPER(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(B4,FIND("@",B4)-1)),"_","."),"-","."),"9","."),"8","."),"7","."),"6","."),"5","."),"4","."),"3","."),"2","."),"1","."),"0","."),"/","."),"..",". ")," ."," ")),". ","."),FIND(".",SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(B4,FIND("@",B4)-1)),"_","."),"-","."),"9","."),"8","."),"7","."),"6","."),"5","."),"4","."),"3","."),"2","."),"1","."),"0","."),"/","."),"..",". ")," ."," ")),". ","."))-1))

    The formula in the cell in Column E is as follows:

    =UPPER(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(B4,FIND("@",B4)-1)),"_","."),"-","."),"9","."),"8","."),"7","."),"6","."),"5","."),"4","."),"3","."),"2","."),"1","."),"0","."),"/","."),"..",". ")," ."," ")),". ","."),LEN(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(B4,FIND("@",B4)-1)),"_","."),"-","."),"9","."),"8","."),"7","."),"6","."),"5","."),"4","."),"3","."),"2","."),"1","."),"0","."),"/","."),"..",". ")," ."," ")),". ","."))-FIND(".",SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(LEFT(B4,FIND("@",B4)-1)),"_","."),"-","."),"9","."),"8","."),"7","."),"6","."),"5","."),"4","."),"3","."),"2","."),"1","."),"0","."),"/","."),"..",". ")," ."," ")),". ","."))))

  75. Kishan says:

    1 '=REPLACE(LEFT(B4,FIND("@",B4)-1),FIND("_",B4),1," ")
    2 '=REPLACE(LEFT(B5,FIND("@",B5)-1),FIND(".",B5),1," ")
    3 '=SUBSTITUTE(LEFT(B6,FIND("@",B6)-1),"321"," ")
    4 '=SUBSTITUTE(LEFT(B7,FIND("@",B7)-1),"___"," ")
    5 '=REPLACE(LEFT(B8,FIND("@",B8)-1),FIND("-",B8),1," ")
    6 '=SUBSTITUTE(LEFT(B9,FIND("@",B9)-1),"999"," ")
    7 '=REPLACE(LEFT(B10,FIND("@",B10)-1),FIND("0",B10),1," ")

  76. Anwar Alrubeidy says:

    A1= Email Address B1= First Name
    B2 =LEFT(A2,SEARCH(CHAR(H2),A2)-1) =John
    A3 =
    B3 = LEFT(A3,SEARCH(CHAR(H3),A3)-1) =John
    A4 =
    B4 =LEFT(A4,SEARCH(CHAR(H4),A4)-1) =John
    D1 = FirstName+email
    D2 =REPLACE(A2,1,SEARCH("doe",A2)-1,"")
    D3 =REPLACE(A3,1,SEARCH("doe",A3)-1,"")
    H2 = 95, H3 = 46, H4 = 51, H5 = 64
    C1 = Last Name
    C2 =LEFT(D2,SEARCH(CHAR($H$5),D2)-1) = doe
    C3 ==LEFT(D3,SEARCH(CHAR($H$5),D3)-1)= doe
    C4 ==LEFT(D4,SEARCH(CHAR($H$5),D4)-1)= doe

    Hope this helps.

  77. Sam says:

    B1: =left(A1,find("@",A1)-1)
    C1: =left(A1,4)
    D1: =mid(A1,find("@",A1)-3,3)

  78. Siva says:

    Given below are the macro codes to extract the name portion, first name and last name based on the given emails :-

    Sub NameExtractionFromEmail()

    Dim cell As Range
    Dim tempVal As String, NamePortion() As String
    Dim Num As Integer
    Dim Firstname As String, LastName As String
    Dim LastRow As Long
    Dim tempName As Long

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For Each cell In Range(Cells(4, 2), Cells(LastRow, 2))
    tempVal = cell.Value
    NamePortion() = Split(tempVal, "@")
    cell.Offset(0, 1).Value = NamePortion(LBound(NamePortion))

    For Num = 1 To 96
    tempName = InStr(cell.Offset(0, 1).Value, Chr(Num))
    If tempName > 0 Then
    Firstname = Left(cell.Offset(0, 1).Value, tempName - 1)
    cell.Offset(0, 2).Value = Firstname
    LastName = Right(cell.Offset(0, 1).Value, tempName - 2)
    cell.Offset(0, 3).Value = LastName
    End If
    End Sub

  79. Mari A. says:

    For the given example name sample:

    First Name: =REPLACE(B2, 5, 9, " ")

    Last Name: =RIGHT(B2, 3)

    I kept it simple, however, I am still working on how to get ONE formula for any name length.

  80. Anwar says:

    A B C
    1-Email Name Portion CODE john_doe 95 john.doe 46 john321doe 51 John___doe 95 john-doe 45 john999doe 57 john0doe 48

    First Name
    Last Name
    Change B2 and C2 to B3,B4,etc,and C3,C4,etc.

  81. Dhiti says:




    It's too long. Sorry, I'm just starter. Thank you for your useful website and for your quiz.

  82. suyash says:

    why ppl making it too complicated
    for ex.

    1st step extract "john_doe"=LEFT(B4,(FIND("@",B4)-1))
    2nd step extract john= =LEFT(F4,FIND("n",F4))
    3rd step extract doe= =RIGHT(F4,3)

  83. Mukesh says:

    You may refer this also.
    =++LEFT(B3,4)&" "&LEFT(RIGHT(B3,13),3)
    =++LEFT(B4,4)&" "&LEFT(RIGHT(B4,13),3)
    =++LEFT(B5,4)&" "&LEFT(RIGHT(B5,13),3)


  84. Ata says:

    First Name: =LEFT(LOWER(LEFT(B4,FIND("@",B4)-1)),MIN(IFERROR(FIND(CHAR(ROW($A$1:$A$95)),LOWER(LEFT(B4,FIND("@",B4)-1))),FALSE))-1)


  85. sandeep kelakr says:

    Date Particulars Voucher No. Voucher Ref. & Date Narration Quantity Rate Value Gross Total SALES RETURN Carrige Outward Sales Return Gst 5% Output Cgst 2.5% Output Sgst 2.5%
    01-May-2019 Haribhau Ramkisan Gadgule 825 spcod-0363 / 1-5-2019 4 Unit Fodder Grass Gopi Krishna 50 gm Rs. 385-Santosh Bhosale CLUSTER2 4.00 pkt 1540.00 1540.00 1540.00
    Fodder Grass - Gopikrishna - 50 Gm - Amar Seed 4.00 pkt 385.00/pkt 1540.00
    01-May-2019 Vitthal Raisingh Daberao 826 spcod-0411 / 1-5-2019 4 Unit Fodder Grass Gopi Krishna 50 gm Rs. 385-Anjali Barge CLUSTER2 4.00 pkt 1540.00 1540.00 1540.00
    Fodder Grass - Gopikrishna - 50 Gm - Amar Seed 4.00 pkt 385.00/pkt 1540.00
    01-May-2019 Chandrakant Tukaram Borate 827 spcod-0330 / 1-5-2019 2 Unit Fodder Grass Gopi Krishna 50 gm Rs. 385-Shridhar Dhembare CLUSTER2 2.00 pkt 770.00 770.00 770.00
    Fodder Grass - Gopikrishna - 50 Gm - Amar Seed 2.00 pkt 385.00/pkt 770.00
    01-May-2019 Vaibhav Ram Kadam 828 SPCOD-0681 / 1-5-2019 1 Unit Fodder Grass Bajra No 1 1 kg Rs. 495+30-Nayana Hule CLUSTER2 1.00 pkt 495.00 525.00 495.00 30.00
    FODDER BAJRA No.1-Maxim Seed-1kg 1.00 pkt 495.00/pkt 495.00
    01-May-2019 Ujjawal Dhanpal Ladhe 829 SPCOD-0587 / 1-5-2019 1 Unit PGR Induce G 500 ml Rs. 525+30-Urmila Sawant CLUSTER2 1.00 NOS 500.00 555.00 30.00 500.00 12.50 12.50

    above is sample i want to row no 2 in front of row no1
    then narration contents name also should be in separate colu

    pls suggest

Leave a Reply

« »