VBA To Extract Email Address From Text

[Rick Rothstein]

I go to Visual Basic - Insert - New Module, I paste the code then I hit F5 then i get this prompt (see attached)

The installation part (where you paste it) is correct, but as I said previously, what you pasted is not a macro... it is a function and you it just like any other function in VB. For example, you have no problem using, say, the Int function, do you? My guess is no... just do the same thing with either my function or the one Excel Fox posted, but use the name of whichever function you pasted instead of Int. Functions, whether built-in or written by me, Excel Fox, or any one else, all do the same thing... they usually (but not always) let you pass in values (called arguments) and the all return a value which can be used directly inside an expression (in VBA) or in a formula in a worksheet cell.

[dunndealpr]

Got it. So once I've pasted the code into a module, what do I do to run it?

[Rick Rothstein]

You do not run functions (you run macros), you use functions to return a value to you. Once you have pasted (I'll assume my code because I'll be using my function's name below) into the module, go to any worksheet and put this in cell A1...

This is a test line with this (one.two@three.four.com) strange email address

Now, put this in cell A2 and I think all will become clear (well, I hope so because I am going to sleep now)...

=GetEmailAddress(A1)