Link to home
Create AccountLog in
Avatar of finnstone
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 !
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Does every row has just on @ symbol? If yes, you can do it with formulas only. VBA is not quite required I guess.

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.
Avatar of finnstone
finnstone

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@
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Hi,

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)))

Open in new window

Shums, it says typo in formula
wow the udf works
Try now:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))

Open in new window