Reputation: 32622
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
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
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
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
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