CODEWITHSUNDEEP
excelvba
BruceWayne
BruceWayne

Reputation: 23283

Extract URL from =Hyperlink()

I'm trying to extract the formula that is evaluated from a link generated with a formula:

=HYPERLINK("https://www.google.com/search?q="&A1&B1,"Link")
(A1 has vba and B1 has help)

I've seen many, many threads and even some SO threads with suggestions, but they only get me to the ...q= without considering I have more text to come.

The best luck I've had so far is from this thread, and I've tweaked it to search until B1 like this:

...
S = Left(S, InStr(S, "B17") + 2)
...

But it returns https://www.google.com/search?q="&A1&B1.

How can I get it to first evaluate what's in those cells, before returning the URL?

Upvotes: 1

Views: 1537

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

I was overthinking this I think. Thanks to @MacroMan for getting my head straight. I put together the following, rather clunky, macro.

Function hyperlinkText(rg As Range) As String
' Inspired by https://stackoverflow.com/questions/32230657/extract-url-from-excel-hyperlink-formula/32233083#32233083
Dim sFormula As String
Dim Test As String

sFormula = rg.Formula
Test = Mid(sFormula, WorksheetFunction.Search("""", sFormula), WorksheetFunction.Search(",", sFormula) - WorksheetFunction.Search("""", sFormula))
hyperlinkText = Evaluate("=" & Test)
End Function

This can take a URL that looks like:
=HYPERLINK("https://www.google.com/search?q="&A17&B17,"Link") and return the evaluated URL:

enter image description here

Upvotes: 4

Related Questions