We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

remove everything in an excel cell, except for the email address

1,884 Views
Last Modified: 2017-03-29
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 !
Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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@
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
Shums, it says typo in formula

Author

Commented:
wow the udf works
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

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

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.