Extract URL from anchor Text in Excel

I’ve looked through this thread for something that fits my query but most of the topics came close but wasn’t specific to mine.

I’m trying to extract the URL from an anchor text in Excel.

Is there a formula for this? When I try =GETURL or =HYPERLINK it still puts in the anchor text with the URL bound to it.

Any ideas? Macro solutions are pretty useless so a formula would be sweet. Otherwise, lay the macros on me.

If you have a cell that contains a hyperlink and you wish to see the URL rather than the “friendly name”, then consider the following User Defined Function (UDF):

Public Function hyp(r As Range) As String
    hyp = ""
    If r.Hyperlinks.Count > 0 Then
        hyp = r.Hyperlinks(1).Address
        Exit Function
    End If
    If r.HasFormula Then
        rf = r.Formula
        dq = Chr(34)
        If InStr(rf, dq) = 0 Then
        Else
            hyp = Split(r.Formula, dq)(1)
        End If
    End If
End Function

This UDF works with both Inserted Hyperlinks and =HYPERLINK() functions.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
   ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=hyp(A1)

To learn more about macros in general, see:

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

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!