cowabunga!!!!!! THANKS RON, THIS WORKED GREAT!!!!

"Ron Rosenfeld" wrote:

On Sun, 3 Feb 2008 08:33:02 -0800, Ralph
wrote:

Ron and everyone, I'd love to try your macros but I don't know how to enter
them into excel, my apologies, can anyone take the time to explain to me how
to take this code and put it into a macro?

also Ron, the a1:e100 is a bit limited for my purposes would it be possible
to expand the range from like say a1 to L1000?



To enter a macro:

<alt-F11 opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then, from
the top menu bar, select Insert/Module and paste the code below into the window
that opens.

See the notes below about changing your source and destination.

===========================================
Option Explicit
Sub ExtractEmails()
Dim c As Range
Dim rDest As Range
Dim str As String
Dim i As Long
Dim re As Object, mc As Object, m As Object

i = 1
Set rDest = [m1]
rDest.EntireColumn.ClearContents
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
'This regex probably complies with RFC 2822 and matches all
'country code top level domains, and specific common top level domains.
re.Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+" & _
"(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@" & _
"(?:[a-z0-9](?:[a-z0-9\-]*[a-z0-9])?\.)+" & _
"(?:[A-Z]{2}|com|org|net|gov|mil|biz|info|name" & _
"|aero|biz|info|mobi|jobs|museum)\b"
For Each c In Selection
str = c.Value
Set mc = re.Execute(str)
For Each m In mc
rDest(i, 1).Value = m.Value
i = i + 1
Next m
Next c
End Sub
=====================================

The code above as been written to work on "Selection" instead of a hard coded
range. So just select the range you wish to process.

Change rDest to reflect where you want the results. The routine will clear the
entire column first, so either change this or don't have anything else in that
column. rDest can also be on a new or different sheet.

After making the appropriate changes, <alt-F8 opens the Macro Dialog Box.
Select the Macro and <RUN.
--ron