VBA To Extract Email Address From Text

[snb]

But that was yesterday....

[Excel Fox]

ohohoho.... I didn't know snb could take the slippery side too

[Rick Rothstein]

But that was yesterday....

That is too funny!

[Rick Rothstein]

I assumed the OP had email addresses in sentences, so I assumed every email address being encapsulated by spaces. Based on his feedback I conclude my assumption was correct.

By the way, if you are correct and the OP's email addresses are separated by spaces, and if (and this is a big if) the email address will always come before any other text (Twitter or whatever) with an asterisk, and ignoring the fact the OP said he wanted a VBA solution, there is a formula solution to be had...

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

[dunndealpr]

hey all! been working with these codes a couple of days. Rick's most recent 'getemailaddress' code results in #NAME? straight down the line for me.
Excel Fox's most recent 'extractemail' code works, but the email address themselves are not reflected in the formula fields. Instead of seeing the email address in the formula field up top I see the formula itself. Is this normal?

[dunndealpr]

I've also noticed the 'extractemail' code is missing quite a few emails. I've attached a document with three examples of text it was unable to extract emails from. Any ideas?

[Rick Rothstein]

hey all! been working with these codes a couple of days. Rick's most recent 'getemailaddress' code results in #NAME? straight down the line for me.

Just so you know, my code works fine, even against the email addresses in the file you posted in Message #36 (I just tested it). If you are getting a #NAME? error, it is because you did one of two things wrong... either you put the code in the wrong place (it needs to go in a general Module, the same kind of module that macros go in) or you spelled the function name incorrectly when you used it in your formula. I will say, though, that my function (and I would guess most other functions) will return the wrong text for the first email address in your posted file, namely this one...

"thanks for the email man hit me at moneymankp@yahoo.com.LOOKING FOR MANAGEMENT!!!"

My function will return moneymankp@yahoo.com.LOOKING as the email address, not moneymankp@yahoo.com which is what you would be wanting it to. Notice that the quoted text above also assumes moneymankp@yahoo.com.LOOKING is the email address (it should be highlighted in blue and underlined)... that is because an email address can have more than one dot after the @ sign and because LOOKING are all valid characters for use in an email address.

Excel Fox's most recent 'extractemail' code works, but the email address themselves are not reflected in the formula fields. Instead of seeing the email address in the formula field up top I see the formula itself. Is this normal?

It sounds like you put the formula in a cell formatted as TEXT. Try changing the cell format to General and then re-enter the formula.

[dunndealpr]

Thanks Rick! Both suggestions worked like a charm.

[Excel Fox]

My function will also return the same email address that Rick's returns, ie moneymankp@yahoo.com.LOOKING

Again, one of the patterns

\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b 

suggested in the url link I posted in post # 6, gives an output that you'd normally expect, ie. moneymankp@yahoo.com

Of course in this case, you won't get email address that contain two dots (.)

That's a trade-off decision user has to take.

[dunndealpr]

Just so you know, my code works fine, even against the email addresses in the file you posted in Message #36 (I just tested it). If you are getting a #NAME? error, it is because you did one of two things wrong... either you put the code in the wrong place (it needs to go in a general Module, the same kind of module that macros go in) or you spelled the function name incorrectly when you used it in your formula. I will say, though, that my function (and I would guess most other functions) will return the wrong text for the first email address in your posted file, namely this one...

"thanks for the email man hit me at moneymankp@yahoo.com.LOOKING FOR MANAGEMENT!!!"

My function will return moneymankp@yahoo.com.LOOKING as the email address, not moneymankp@yahoo.com which is what you would be wanting it to. Notice that the quoted text above also assumes moneymankp@yahoo.com.LOOKING is the email address (it should be highlighted in blue and underlined)... that is because an email address can have more than one dot after the @ sign and because LOOKING are all valid characters for use in an email address.



It sounds like you put the formula in a cell formatted as TEXT. Try changing the cell format to General and then re-enter the formula.

Hey Rick, this workaround for the cell formats has not been working for me. In the actual field I see the email address, but in the formula field I'll see something to the effect of =extractemail(F2035), and selecting the General cell format both before and after applying the formula does not change this. My only option has been to save the file as a csv, but that also removes all kinds of highlighting that I have on the files. Do you have any other ideas about how I can fix this problem?

Thanks again for your help with all this.