Results 1 to 10 of 10

Thread: (Urgent Request) Extract first name and last name from Email ID
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2014
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default (Urgent Request) Extract first name and last name from Email ID

    Dear all,
    I have a simple requirement.
    Suppose i have few mail ID's as below,
    aaaa.bbbbbb@xx.com
    ccccccc.dd@xx.com
    eeeeeeeeeeee.f@xx.com

    I want the vba code and extract firstname and last name from the these mail ID's

    Note: From beginning till special character "." are my first names.
    And from "." till special character "@" are my second names.

    Please provide me the code.
    Thanks
    Madvesh

  2. #2
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Sorry, it wasn't vba ...
    Regards Matt
    ___________________________________________________________
    *The difference between Men and Boys is the price of their toys*

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Any alternatives? ?

  4. #4
    New Member
    Join Date
    Nov 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Quote Originally Posted by mchilapur View Post
    Dear all,
    I have a simple requirement.
    Suppose i have few mail ID's as below,
    aaaa.bbbbbb@xx.com
    ccccccc.dd@xx.com
    eeeeeeeeeeee.f@xx.com

    I want the vba code and extract firstname and last name from the these mail ID's

    Note: From beginning till special character "." are my first names.
    And from "." till special character "@" are my second names.

    Please provide me the code.
    Thanks
    Madvesh
    Hai, I have Normal excel formula to get First name & Last name: Assume that your mail Id in A2 Cell, Type the rormula 1st Name =LEFT(A2,SEARCH(".",A2)-1) and for Last Name =MID(A2,SEARCH(".",A2)+1,SEARCH("@",A2)-SEARCH(".",A2)-1)

  5. #5
    Board Regular
    Join Date
    Apr 2014
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Thanks for your quick response.
    That works perfectly fine...Is there any way to use this function into VBA code and put these names in 2 variables by name "FirstName" and "LastName"...???

  6. #6
    Board Regular
    Join Date
    Apr 2014
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Actually i have a list of email ID in a variable. I just want to extract first and last name from each mail ID and put them in a new variable called "FullName".Thats what my intent is..
    Plz refer below link
    http://www.mrexcel.com/forum/excel-q...ific-user.html

    Now i am in process of converting this mail ID into Full name (First name and last name) and compare it with Excel Author name...
    Could you please check it??
    Last edited by mchilapur; Nov 16th, 2014 at 09:10 AM.

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    mchilapur,

    The following macro is based on the raw data that you have displayed.

    Sample raw data:

    Excel 2007
    ABC
    1aaaa.bbbbbb@xx.com
    2ccccccc.dd@xx.com
    3eeeeeeeeeeee.f@xx.com
    4

    Sheet1





    After the macro:

    Excel 2007
    ABC
    1aaaa.bbbbbb@xx.comaaaabbbbbb
    2ccccccc.dd@xx.comcccccccdd
    3eeeeeeeeeeee.f@xx.comeeeeeeeeeeeef
    4

    Sheet1





    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code
    2. Open your NEW workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code:
    Sub ExtractNames()
    ' hiker95, 11/16/2014, ME818525
    Dim c As Range, s1, s2
    Application.ScreenUpdating = False
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      s1 = Split(c, "@")
      If InStr(s1(0), ".") Then
        s2 = Split(s1(0), ".")
        c.Offset(, 1).Resize(, 2).Value = s2
      Else
        c.Offset(, 1).Value = s1(0)
      End If
    Next c
    Columns("B:C").AutoFit
    Application.ScreenUpdating = True
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ExtractNames macro.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  8. #8
    Board Regular
    Join Date
    Apr 2014
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Hello Hiker...The output is as per my intent.
    But can u set these First and last names into two different variables? ?
    Or plz lookinto my below post and help me to resolve it.

    http://www.mrexcel.com/forum/excel-q...ific-user.html

    Thanks for everything...

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    mchilapur,

    I have looked at your other thread, and, I have misunderstood your current request.

    Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: (Urgent Request) Extract first name and last name from Email ID

    Quote Originally Posted by hiker95 View Post
    Code:
     Sub ExtractNames()
    ' hiker95, 11/16/2014, ME818525
    Dim c As Range, s1, s2
    Application.ScreenUpdating = False
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      s1 = Split(c, "@")
      If InStr(s1(0), ".") Then
        s2 = Split(s1(0), ".")
        c.Offset(, 1).Resize(, 2).Value = s2
      Else
        c.Offset(, 1).Value = s1(0)
      End If
    Next c
    Columns("B:C").AutoFit
    Application.ScreenUpdating = True
    End Sub
    @hiker95,

    I think you can "simplify" your code a little bit like this...

    Code:
    Sub ExtractNames()
      Dim Cell As Range, Names() As String
      Application.ScreenUpdating = False
      For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Names = Split(Split(Cell, "@")(0) & ".", ".")
        Cell.Offset(, 1).Resize(, 2) = Names
      Next
      Columns("B:C").AutoFit
      Application.ScreenUpdating = True
    End Sub

    @mchilapur

    Using the code I posted above, the first name is in Names(0) and the last name is in Names(1).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •