CODEWITHSUNDEEP
excelexcel-formula
user1038814
user1038814

Reputation: 9677

Get names from email column

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

Upvotes: 1

Views: 5275

Answers (5)

Mohamed Rizmi
Mohamed Rizmi

Reputation: 1

Enter the first Row with the expected results, then Press CTRL+E boom! You're done (Flash Fill)

Upvotes: 0

Sasmit
Sasmit

Reputation: 160

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

Upvotes: 1

barry houdini
barry houdini

Reputation: 46451

Surname can be extracted with this version

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

Upvotes: 1

Peter Albert
Peter Albert

Reputation: 17515

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

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

Upvotes: 3

weir
weir

Reputation: 4771

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)

Upvotes: 2

Related Questions