84

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 = "http://www.mylocation.com"

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) = "http://www.mylocation.com"

2
  • 1
    Actual hyperlinks (Insert>>Hyperlink) or hyperlinks using =HYPERLINK() ?
    – Tim Williams
    May 9, 2013 at 0:06
  • Does it have to be a formula? There are lots of VBA solutions.
    – CharlieRB
    May 9, 2013 at 14:03

5 Answers 5

43

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)

http://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

3
  • 1
    Works like a charm! Place this snippet in a new "module" in the VBA of your Excel file (not just the sheet).
    – Konerak
    Nov 10, 2021 at 10:55
  • 1
    Thank you for the simple solution, Matthew! For me, the result truncates when it encounters a hashtag (#). Any idea how to modify the code to account for that?
    – SAL
    Nov 30, 2021 at 20:24
  • 2
    @SAL see gilly3's comment in the answer below. I also added an answer with code.
    – Trashman
    Feb 11 at 16:58
74

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
  • 5
    @igor Why are you using "range("a1") in cell.range("A1").Hyperlinks.Count and cell.range("A1").Hyperlinks(1).Address ? I've copied your macro but took it out (-->cell.hyperlinks.count and cell.hyperlinks(1).adress) and it works perfectly. I'm curious on the purpose of the "range("a1") here.
    – P. O.
    May 9, 2013 at 17:11
  • 1
    This suppose to treat if there are multiple links inside the hyperlink. it was just an idea, great it worked with your adjustments. :)
    – Igor O
    May 9, 2013 at 17:22
  • 3
    If your hyperlink includes a document fragment (eg, #something), that is stored in the SubAddress, so you will need to concatenate Address, "#", and SubAddress.
    – gilly3
    Mar 19, 2018 at 0:05
  • Since parameter cell is a range object it can contain more than one cell. Range("A1") could be rewritten as Cells(1). It instructs the function to only consider the first cell in parameter cell.
    – ChrisB
    Jun 22, 2018 at 0:40
  • 1
    Thank you so much! That worked better than in Google Sheets (which is rare case lol:) Important things to not forget: save Excel file as a Macro enabled workbook; Insert formula in new module (not in sheet or workbook code); name of function is case sensitive.
    – Liker777
    Jan 29, 2020 at 19:40
2
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
  • In other that leave off the quote marks pass in the cell reference by using this in the spreadsheet =EXTRACT_URL(ADDRESS(row(A4),1,4)) . Otherwise you have to hardcode the range reference. Then you can copy this function from one cell to another. Jun 14, 2019 at 11:41
2
  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
1

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
  • Not sure how a range can have multiple hyperlinks, unless more than one cell is selected, in which case, does concatenating them all actually work? Otherwise, thank you for accounting for the hashtag!
    – SAL
    Feb 15 at 0:43
  • 1
    @SAL not normally. at least if the range is just a cell, at least not through the normal UI. However, if the range is more than just a cell, there could be links in each cell. So I accounted for it in the code. If there is only one hyperlink, without or without a SubAddress, this will work directly as a link. If you have multiple hyperlinks, you'd have to parse them with another function or command. You couldn't directly use them in a link that way, but at least all the data would be in the string produced.
    – Trashman
    Feb 15 at 1:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.