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

mchilapur

Board Regular
Joined
Apr 14, 2014
Messages
126
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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)
 
Upvote 0
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"...???
 
Upvote 0
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-...e-provide-automatic-pop-up-specific-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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top