How to Extract hyperlink addresses in Excel data

Quite often I find a number of website addresses embedded in my excel spreadsheets, and to get the actual URL to show has been a problem.

Today I decided to work it out.

My first port of call was the Microsoft Site itself at: Macro to extract data from a chart in Excel

Maybe you need more excel knowledge than I have to do this, but that explanation sort of lost me, and didn’t work.

I kept looking around, and tried another three options that I found, each of them either too confusing or it just didn’t work for me.

Finally, after combining information from a few sites, I hit on this workaround:

In the workbook that contains the relevant starting data, do the following:

1. Press Alt+F11
2. Select Insert, followed by the Module option

In the box that then appears, copy the following code:

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String
Dim ST2 As String

If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If

HyperLinkText = ST1

End Function

This creates a function that can then be used in the spreadsheet by doing the following:

Select the cell where you want the URL to actually appear, and type =HyperLinkText(B1) into that cell. The B1 reference will need to be changed to the cell that holds your link with the hidden URL.

That formula will then extract the hidden URL, and show it in your selected cell.