Extracting data out of hyperlinks (minor problem)

[uesc]

i have a small problem.
i have an excel spreadsheet, an within that spreadsheet is a column o
names, first and last in the same column. Each person's name has thei
e-mail address attached to it in the form of a hyperlink. i would lik
to extract the e-mail addresses out and into a new spreadsheet withou
editing each hyperlink, copying the e-mail address, and pasting it int
a new spreadsheet.

i don't know much about excel, but there has to be a way to do this
right?

Thanks in advance helping a n00b

 

[Peo Sjoblom]

It would definitely be easier if you provided a couple of examples how the
hyper links
can look?

 

[uesc]

no problem.
i did a quick sample file and took a pic of it.
i hope this helps
when i took the picture my mouse was over the last cell in the list

[image: http://img.villagephotos.com/p/2003-5/185413/sample.jpg]

 

[Peo Sjoblom]

If they are from the insert>hyperlink you can use this function from David
McRitchie

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function


press Alt + F11, click insert>module and paste the above, then press Alt + Q
to close VBE
then use it like


=SUBSTITUTE(hyperlinkaddress(A1),"mailto:","")

 

[uesc]

Peo:

You are a genius!
that worked like a charm on all of the addresses!!

Thank you so much!