Harvesting URL data from cells

N

NikkJ

I have a list of 200 or so cells which are each hyperlinked to individual
web pages.
I would like to generate a list of the actual hyperlink addresses rather
than the visible cell content. The only way I seem to be able to do it is to
open each page individually and then cut 'n paste the URL?

Thanks
 
G

Gord Dibben

You can employ a User Defined Function.

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count > 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

Usage is: =HyperlinkAddress(cellref)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown above.


Gord Dibben Excel MVP
 
N

NikkJ

Thanks Gord.

I took a crash course in UDF insertion and use. Another one in the
=CONCATENATE function.

It worked perfectly.

Thanks for the swift and accurate advice.

NikkJ
 
G

Gord Dibben

Thanks for the feedback.

If I recollect, David was also the author of the UDF I posted.


Gord
 
Top