Excel ? to change long list of Hyperlinks to URL's?

I have a list of several hundred hyperlinks (with aliases?) that I'd like to be able to somehow display their URL addresses. I know that I can rightclick, hyperlink, copy hyperlink, and then paste. But, that works only for one cell at a time. With such a large group, how can I do all of these in a "group" type operation??

try this:

press alt+F11, then Insert|Module, paste this into the module:

Function getURL(alias)
getURL = alias.Hyperlinks.Item(1).Address
End Function

"alias" is the cell with the hyperlink. I'm assuming you only wanted the URL as text and not have it be a hyperlink as well. If this is not the case let me know.

giacomo

Thanks Giacomo,

Thanks for your help. Unfortunately, due to my own VB ineptitude, I couldn't fully figure out the instructions you suggested to work. Another set of instructions I received however I did get to work. If your interested, here's what I used.

Public Function GetAddressLink(Rng As Range)
GetAddressLink = ""
For Each Hl In ActiveSheet.Hyperlinks
Set i = Intersect(Hl.Range, Rng)
If i Is Nothing Then
Else
GetAddressLink = Hl.Address
Exit For
End If
Next
End Function

- Open your workbook
- Press Alt+F11 keys
- Insert a Module (Insert Menu>Module command)
- Paste the above code
- If an hyperlink stay on A1, on B1 insert =GetAddressLink(A1)
- Then fill down

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

? to change long list of Hyperlinks to URL's?

Re: ? to change long list of Hyperlinks to URL's?

Re: ? to change long list of Hyperlinks to URL's?