Extract Link Location of a Hyperlink in Excel – Using Formulas

hyperlinkmicrosoft excel

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"

Best Answer

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

Related Question