Extracting URL from hyperlink cell

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Using Office 2003 Excel, have cells with hyperlinks in
them pasted from the web, but what shows is the link
description.

Is there a function to extract the url itself, so that it
shows instead of the description? I want this so that I
can sort links from the same website together.
 
From a previous post:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top