Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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"

share|improve this question

migrated from stackoverflow.com May 9 '13 at 13:51

This question came from our site for professional and enthusiast programmers.

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

2 Answers 2

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
    
share|improve this answer
    
@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. Obertelli May 9 '13 at 17:11
    
This suppose to treat if there are multiple links inside the hyperlink. it was just an idea, great it worked with your adjustments. :) –  Igor May 9 '13 at 17:22

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.