asked on 3/29/2017

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 !

VBA Microsoft Excel Microsoft Office

Last Comment

Shums Faruk

3/29/2017

Nitin Sontakke

3/29/2017

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.

finnstone

3/29/2017

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

Rgonzo1971

3/29/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Shums Faruk

3/29/2017

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

finnstone

3/29/2017

ASKER

Shums, it says typo in formula

finnstone

3/29/2017

ASKER

wow the udf works

Shums Faruk

3/29/2017

Try now:

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

Open in new window