Extract URL from =Hyperlink()

Question

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?

Have you tried building the string in a helper column and seeing if that makes a difference? Even just to rule out of it's the formula or the data (if that makes sense) — SierraOscar, Mar 02 '17 at 19:49
@MacroMan - ...!!! If I quickly extract the text in between `(` and the first comma, I think I can get it that way. Dang, I've been thinking of extracting from the formula too long. I think I can get this then with `Mid()`. Let me try it out. Thanks for the idea! Edit: wait, hm. I have to extract it, while keeping the references, and put it in a cell so it can evaluate. Perhaps I can use `Evaluate()`? — BruceWayne, Mar 02 '17 at 19:51
You can wiggle your way around this using `split()` also. Something like `Split(t, Chr(34))(1) & Range(Split(t, "&")(1)).Value & Range(Left(Split(t, "&")(2), 2)).Value` — JNevill, Mar 02 '17 at 20:21

score 4 · Accepted Answer · answered Mar 02 '17 at 20:04

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:

Extract URL from =Hyperlink()

1 Answers1

Linked