Get names from email column

Question

I have a column made up of emails. MOst of these are in the following format:

[email protected]

Is there any way I can extract the 'name' and populate column 2, and extract 'surname' and populate column 3 using an excel formula?

So the columns would read:

[email protected]       name         surname

Any help is much appreciated.

Cheers

[This existing question][1] shows how to break out substrings in Excel. [1]: http://stackoverflow.com/questions/6133287/how-to-extract-the-last-substring-from-a-excel-column — , Jan 29 '13 at 14:25

score 3 · Answer 1 · answered Jan 29 '13 at 14:27

3

Name: =LEFT(A1,SEARCH(".",A1)-1)

Surname: =MID(A1, SEARCH(".",A1)+1,SEARCH("@",A1)-SEARCH(".",A1)-1)

answered Jan 29 '13 at 14:27

Peter Albert

16,917
5
64
88

1

Damn, beat me to it ;) Except mine used FIND. Now I have to go read up on the difference between FIND and SEARCH ;) Edit: Found it -> Case sensitivity. – isotrope Jan 29 '13 at 14:29
FIND is case-sensitive, SEARCH isn't, so for punctuation searches it makes no difference which you use..... – barry houdini Jan 29 '13 at 14:31
Must have been a photo finish! ;-) – Peter Albert Jan 29 '13 at 14:32

score 2 · Accepted Answer · answered Jan 29 '13 at 14:25

2

I would use the Left and Mid functions to get columns 2 and 3, respectively. I will write out the formula for you in a minute...

Column 2:

=LEFT(A1,FIND(".",A1)-1)

Column 3:

=MID(A1,FIND(".",A1)+1, FIND("@",A1)-FIND(".",A1)-1)

answered Jan 29 '13 at 14:25

weir

4,521
2
29
42

score 1 · Answer 3 · answered Jan 29 '13 at 14:30

1

Surname can be extracted with this version

=REPLACE(LEFT(A1,FIND("@",A1)-1),1,FIND(".",A1),"")

answered Jan 29 '13 at 14:30

barry houdini

45,615
8
63
81

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

1

Considering the format is [email protected], the first row is your header row and you need the first and last name in proper format, here's the formula

First Name

=PROPER(MID(A2,1,FIND(".",A2)-1))

Last Name

=PROPER(MID(A2,FIND(".",A2)+1,FIND("@",A2)-FIND(".",A2)-1))

Assuming your email id is in A2 and the First Name and Last Name needs to be extracted in B2 and C2 respectively

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '14 at 17:59

Sasmit

160
1
10

Get names from email column

4 Answers4

First Name

Last Name