Extract URL from a cell having =Hyperlink() formula applied to it

Question

I want to fetch the URL from a cell which has this formula applied to it.

=HYPERLINK(CONCATENATE("https://loremipsum.com/#/Advertiser/",[@[Customer CID]],"/.html"), "View")

The formula has a structured reference to one of the columns in my sheet, 'Customer CID'.

When I try to apply this macro to my sheet it gives the default_value even when the formula is evaluating a correct URL.

Function GetURL(cell As Range, Optional default_value As Variant)
      If (cell.Range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.Range("A1").Hyperlinks(1).Address
      End If
End Function

But when I do not apply the formula and add a Hyperlink to the cell by right-clicking the cell, the macro funtion =GetUrl([@[Customer CID]], "") works and gives me the URL.

Does anyone know how can I perform this task to fetch Hyperlink from a cell if that cell is evaluating the hyperlink from a formula ??

Answer 1

There's no direct way of getting the URL from a cell with a hyperlink generated by a formula. You need to extract the first argument from the HYPERLINK() function, and manually evaluate it.

This is the modified version of your code that does this:

Function GetURL(cell As Range, Optional default_value As Variant)
  With cell.Range("A1")
    If .Hyperlinks.Count = 1 Then
      GetURL = .Hyperlinks(1).Address
    Else
      If Left$(Replace(Replace(Replace(.Formula, " ", ""), vbCr, ""), vbLf, ""), 11) = "=HYPERLINK(" Then
        Dim idxFirstArgument As Long: idxFirstArgument = InStr(.Formula, "(") + 1
        GetURL = Evaluate(Mid$(.Formula, idxFirstArgument, InStrRev(.Formula, ",") - idxFirstArgument))
      Else
        GetURL = default_value
      End If
    End If
  End With
End Function

Note that any extraneous spaces, or added line breaks in the formula are properly accounted for.

Caveats:

• This will only work on formulas with an outermost HYPERLINK() function. (However, every formula can be refactored so that HYPERLINK() is outermost, with only a minor drawback; alternately all formulas can be refactored to one of the forms =IF(…,…,HYPERLINK()) or =HYPERLINK(), with no drawbacks, and only requiring a minor modification to the code; finally, with quite a bit of effort, code could be written to parse any formula no matter where the HYPERLINK() function is situated.);
• If there are any commas after the comma delimiting the first and second arguments of the HYPERLINK() function, the code will break (can be fixed relatively easily).