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:
- Press Alt+F11
- 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
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
HyperLinkText = ST1
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.
227.1 - 670,906