CODEWITHSUNDEEP
excelexcel-2007excel-formula
Himanshu
Himanshu

Reputation: 32622

Getting email address from a cell in excel

How to get email id from a cell which contain more records?

For e.g. Cell A1 has the following value:

google [email protected] www.google.com

I want to copy [email protected] from the cell A1 to B1.

Upvotes: 3

Views: 21297

Answers (4)

ArtiBucco
ArtiBucco

Reputation: 2249

You should use RIGHT, LEFT and FIND functions. This works for me:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))

I used the space character to cut the string but it will only work if the email address is in the middle position.

Upvotes: 6

orrel
orrel

Reputation: 248

For French users with Excel in French, you can use this function :

=SUPPRESPACE(DROITE(SUBSTITUE(GAUCHE(A1;TROUVE(" ";A1&" ";TROUVE("@";A1))-1);" ";REPT(" ";NBCAR(A1)));NBCAR(A1)))

Upvotes: 1

Edward Brey
Edward Brey

Reputation: 41728

This formula finds an email address anywhere within the text (source):

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))

Upvotes: 4

Himanshu
Himanshu

Reputation: 32622

@ArtiBucco's answer is correct. But including TRIM() function will also work on multiple space.

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))

Upvotes: 6

Related Questions