I have a spreadsheet with a large number of cells containing hyperlinks with display text different than the hyperlink location

ie:

cell location: A1

display text = "Site Info"

hyperlink location = ""

Is there an excel formula that allows me to access the text string of the hyperlink location?

Ideally it would look like this:

FORMULA(A1) = ""

2

5 Answers

I only needed to extract the address from a single cell's value so I found this small function handy:

Instead of a "brute force" macro, you could also create a user-defined function that would extract and return the URL for any hyperlink at which it was pointed:

Function GetURL(rng As Range) As String On Error Resume Next GetURL = rng.Hyperlinks(1).Address
End Function

In this case you can place it where you want. If you want, for example, the URL from a hyperlink in A1 to be listed in cell C25, then in cell C25 you would enter the following formula:

=GetURL(A1)

3

You can use a macro:

• Open up a new workbook.
• Get into VBA (Press Alt+F11)
• Insert a new module (Insert > Module)
• Copy and Paste the Excel user defined function below
• Get out of VBA (Press Alt+Q)

• Use this syntax for this custom Excel function: =GetURL(cell,[default_value])

   Function GetURL(cell As range, Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count <> 1) Then GetURL = default_value Else GetURL = cell.range("A1").Hyperlinks(1).Address End If
  End Function

5

function EXTRACT_URL(input) { var range = SpreadsheetApp.getActiveSheet().getRange(input); var re = /^.+?\(\"(.+?)\",.+?$/; if (input.indexOf(':') != -1) { var formulas = range.getFormulas(); for (var i in formulas) { for (var j in formulas[i]) { formulas[i][j] = formulas[i][j].replace(re, "$1"); } } return formulas; } else { return range.getFormula().replace(re, "$1"); }
}

1

1. Open up a new workbook.
2. Get into VBA with Alt+F11 (Fn + Opt + F11 for MAC)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Press F5 and click “Run” Get out of VBA (Press Alt+Q)

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

I had the same issue as @SAL

I found that the .Hyperlink object actually splits the address into .Address and .SubAddress if it encounters a delimiter. "#" is one such delimiter (I don't know if there are others).

See @gilly3 's comment above and this other StackOverflow question:hyperlinks(1).address only returning a partial address

Also Microsoft's (incomplete) documentation on .SubAddress:Hyperlink.SubAddress property

One thing not mentioned anywhere is that the "#" delimiter is not included in either .Address or .SubAddress so if you want an address that will actually work as a full URL, you have to add the "#" delimiter back in.

With that, I modified @Matthew Lock's code as follows, also including the possibility of multiple hyperlinks being stored from @Igor O and my addition of "#":

 Function getURL(rng As Range) As String On Error Resume Next fullURL = "" For Each HL In rng.Hyperlinks If Len(HL.SubAddress) > 0 Then fullURL = fullURL & HL.Address & "#" & HL.SubAddress & " " Else fullURL = fullURL & HL.Address & " " End If Next getURL = fullURL End Function

This should return a text string of all hyperlinks in full (I added a space at the end so if there's more than one, they are separated).

2