Extracting the URL form hyperlinks created from formula

[blimby]

I am using the HYPERLINK function to create a dynamic URL using a formula.

As a simplified example, it would look something like this:

=HYPERLINK("http://baseurl.com/"&4+4, "testlink8")

I can use the UDF below to successfully extract the text portion of the URL but of course it does not evaluate the expression (4+4) and returns http://baseurl.com/, the text between the quotes in the formula, instead of the desired result of http://baseurl.com/8.

I suspect I need to use the EVALUATE function to evaluate the formula before trying to extract the URL but unfortunately my Excel skills are not currently to the task. Can anyone help, please?

Function GetLink(HyperlinkCell As Range)
  Dim s() As String
  If Not HyperlinkCell.HasFormula Then Exit Function
  s() = Split(HyperlinkCell.Formula, """")
  GetLink = s(1)
End Function

 

[Ron Coderre]

Maybe something like this VBA function:

Function GetHlinkAddr(rngHlinkCell As Range)
Dim sFormula As String
With rngHlinkCell
    If .Hyperlinks.Count Then
       GetHlinkAddr = .Hyperlinks(1).Address
        
    ElseIf .HasFormula Then
        sFormula = .Formula
        If InStr(1, sFormula, "HYPERLINK") > 0 Then
            GetHlinkAddr = Evaluate(Mid(Left(sFormula, InStr(1, sFormula, ",") - 1)
                       , InStr(1, sFormula, Chr(34))))
        
        Else
            GetHlinkAddr = "No Hyperlink"
        End If
    End If
End With
End Function

Is that something you can work with?

 

[blimby]

It is Ron, yes, thank you! It works exactly as requested for the example I provided.

Rather than the &4+4, the actual expression I wanted to evaluate was
&INDEX(RiskRatings[Country ID],MATCH(SelectedCountry,RiskRatings[Country]))
which returns a two or three digit number.

I am guessing it's the comma in the expression that is causing the problem. I think I may just be able to make the necessary adjustments myself but grateful for any further assistance. Apologies for not providing the actual expression initially - I was thinking there would be a generic way to evaluate the expression but now believe the issue is extracting the expression from the string.

Thanks again, I really appreciate it.

 

[Ron Coderre]

I see....your expression is more complex...
Try this:

Function GetHlinkAddr(rngHlinkCell As Range)
Dim sFormula As String
With rngHlinkCell
    If .Hyperlinks.Count Then
       GetHlinkAddr = .Hyperlinks(1).Address
        
    ElseIf .HasFormula Then
        sFormula = .Formula
        If InStr(1, sFormula, "HYPERLINK") > 0 Then
            GetHlinkAddr = Evaluate(Mid(Left(sFormula, InStr(1, sFormula, ")),") + 1), InStr(1, sFormula, Chr(34))))
        
        Else
            GetHlinkAddr = "No Hyperlink"
        End If
    End If
End With
End Function

Does that help?

 

[blimby]

Works a treat, thank you so much!