(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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

vasu2007

New Member
Joined
Nov 16, 2014
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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)
 

mchilapur

Board Regular
Joined
Apr 14, 2014
Messages
126

ADVERTISEMENT

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"...???
 

mchilapur

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

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

mchilapur,

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

Sample raw data:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-decoration: underline;color: #0000FF;;">aaaa.bbbbbb@xx.com</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-decoration: underline;color: #0000FF;;">ccccccc.dd@xx.com</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-decoration: underline;color: #0000FF;;">eeeeeeeeeeee.f@xx.com</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;text-decoration: underline;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-decoration: underline;color: #0000FF;;">aaaa.bbbbbb@xx.com</td><td style=";">aaaa</td><td style=";">bbbbbb</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-decoration: underline;color: #0000FF;;">ccccccc.dd@xx.com</td><td style=";">ccccccc</td><td style=";">dd</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-decoration: underline;color: #0000FF;;">eeeeeeeeeeee.f@xx.com</td><td style=";">eeeeeeeeeeee</td><td style=";">f</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;text-decoration: underline;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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).
 

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

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
Top