Extracting Email Addresses

[clarita]

Version: 2008

I have a document with all sorts of information including phone numbers, email addresses, fax numbers etc.... The info is not yet organized (things are all over the place because i had to copy and paste in a from a thousand different documents) and I am wondering if there is a way to extract just email addresses from the mess. Does anyone know if this is possible?

 

[macropod]

Hi Clarita,

Is this an Excel workbook, or a Word document? You've posted in an Excel newsgroup, but your post refers to a document.

If it's a Word document, you can use a wildcard Find/Replace (ie make sure the 'use wildcards' option is checked), with the
following as the Find string:
<[^1-^31\!-\?A-ÿ]{1,}[!\@][^1-\?A-ÿ]{1,}
and nothing as the Replace string. Afterwards, you should have a file with only email addresses in it (and maybe the odd @ and the
following word if you have any loose @ characters in the document).

Of course, if you copy the data from your Excel workbook and paste it into a Word document as unformatted text, you can then use the
above approach.

 

[clarita]

It was an excel spreadsheet but I tried copying and pasting the information to Word like you said. However, the find string doesn't seem to be working as all sorts of things were highlighted that were not email addresses and did not have the @ symbol. I am supposed to use <[^1-^31\!-\?A-ÿ]{1,}[!\@][^1-\?A-ÿ]{1,} correct?

Thank you for your help!

 

[macropod]

Hi clarita,

Humble apologies - the Find/Replace only works when the email address is in Word's hyperlink format. It doesn't work for plain text.

Back to the drawing board ...

Unfortunately, whilst an Excel macro could do what you want quite easily, Office 2008 doesn't support vba.

 

[Laroche J]

Version: 2008

I have a document with all sorts of information including phone numbers, email
addresses, fax numbers etc.... The info is not yet organized (things are all
over the place because i had to copy and paste in a from a thousand different
documents) and I am wondering if there is a way to extract just email
addresses from the mess. Does anyone know if this is possible?

Copy this script and paste it in Script Editor (usually in
Applicatons/AppleScript). Run it while the Excel worksheet to search is in
the Excel front window.


tell application "Microsoft Excel"

-- Select sheet with data to isolate before starting execution of this
script
-- Found e-mail addresses will be copied in the first column of a new
sheet
-- Jean Laroche 2009

set ASh to ActiveSheet
set UR to UsedRange of ActiveSheet
set R1 to Row of first Cell of UR
set RX to Row of last Cell of UR
set C1 to Column of first Cell of UR
set CX to Column of last Cell of UR
set CAd to 1

set SAd to Create New Sheet

Select ASh
repeat with C from C1 to CX
repeat with R from R1 to RX
set V to Value of Cell C of Row R
if V contains "@" then
set Value of (Cell CAd of Column 1 of SAd) to V
set CAd to CAd + 1
end if
end repeat
end repeat

end tell


JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial