Extract URL From Excel Hyperlink Formula

Question

I have an Excel file with hundreds of cells that use the Hyperlink formula =HYPERLINK( <targetURL>, <friendlyName> ). I need to extract the plain text URLs from these. Most examples that I've found rely on the cell using a different hyperlinking method.

So a function like this:

Function HyperLinkText(pRange As Range) As String

   Dim ST1 As String
   Dim ST2 As String

   If pRange.Hyperlinks.Count = 0 Then
      HyperLinkText = "not found"
      Exit Function
   End If

   ST1 = pRange.Hyperlinks(1).Address
   ST2 = pRange.Hyperlinks(1).SubAddress

   If ST2 <> "" Then
      ST1 = "[" & ST1 & "]" & ST2
   End If

   HyperLinkText = ST1

End Function

results in cell text "not found". Alternatively, is there a way of converting these cells to the other hyperlink format so that the macro I have works?

Answer 1

Here is a method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.

If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet.

The formula will return nothing if there is no hyperlink in cell_ref; change to suit.

---

Option Explicit
Function HyperLinkText(rg As Range)
    Dim sFormula As String, S As String
    Dim L As Long
    Dim H As Hyperlink, HS As Hyperlinks

sFormula = rg.Formula
L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare)

If L > 0 Then
    S = Mid(sFormula, L + 11)
    S = Left(S, InStr(S, """") - 1)
Else
    Set HS = rg.Worksheet.Hyperlinks
    For Each H In HS
        If H.Range = rg Then
            S = H.Address
        End If
    Next H
End If

HyperLinkText = S

End Function

---

Answer 2

You could extract it with a regex:

Dim re
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "^=HYPERLINK\(""([^""]+)"""

If re.Test(pRange.Formula) Then
    Debug.Print "URL = " & re.Execute(pRange.Formula)(0).SubMatches(0)
Else
    Debug.Print "URL not found"
End If

This just checks to see if the formula begins with:

=HYPERLINK("

and, if so, grabs the text from that point until the following ".