finnstone
asked on
remove everything in an excel cell, except for the email address
i have long paragraphs including line breaks in each cell. in each cell , there is an email address. i would like to remove everything else so that only the email address remains.
is there some vba that could look for the word that has an @ symbol in it, then remove every other word except for that one? or some other way to do it?
there is no one find or replace that would work !
is there some vba that could look for the word that has an @ symbol in it, then remove every other word except for that one? or some other way to do it?
there is no one find or replace that would work !
ASKER
i think you might be on to something as i can make it that way so that yes there is just one @ symbol which is part of the email i want to get.
i didnt understand the procedure. can you be more specific?
if i have billy@billydomain.com , wouldnt *SPACE@ not find that? it would only find *y@
i didnt understand the procedure. can you be more specific?
if i have billy@billydomain.com , wouldnt *SPACE@ not find that? it would only find *y@
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Hi,
Assuming your data starts from A1, try below formula in B1:
Assuming your data starts from A1, try below formula in B1:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",LEN(A1))),LEN(A1)))
ASKER
Shums, it says typo in formula
ASKER
wow the udf works
Try now:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))
In one cell you can put the Find to get the location of the @ and then find space first backwards and then forward. Then pick the text using Mid function.