Extracting link texts from Excel Hyperlinks

Question

I have a collection of Hyperlinks from an old Excel sheet. I am trying to extract link texts from each Hyperlink using this function

Function GetURL(rng As Range) As String On Error Resume Next GetURL = rng.Hyperlinks(1).Address End Function

But it doesn't work on the old Hyperlinks ( these links are clickable and they work ) however the function works if new Hyperlinks were inserted using

command+k

The difference that I am seeing between the links created both ways are

The old Hyperlinks shows up in the formula bar as

=HYPERLINK("http://www.genome.jp/kegg-bin/show_pathway?ko00620+C00058","ko00620")

And when I use command+k to insert Hyperlinks the formula bar shows just the link name.

How can I extract link texts from

=HYPERLINK("http://www.genome.jp/kegg-bin/show_pathway?ko00620+C00058","ko00620") using macro, Excel VBA?

I see another question and answer here but I do not know how to implement that function.

Function HyperLinkText in the link you have mentioned has to be used in the same way as you are using your GetURL function. Can you elaborate how are you using function GetURL? — Mrig
If you are using Excel 2016, check out the FORMULATEXT function. — jkpieterse
Mrig, thank you, I am using Alt+f11 to bring up VBA. Then insert > module. Copy and paste the function to the module. Later on the Excel sheet use =GetURL for the function. if I use the same method for HyperLinkText what would I call as user defined function? @ jkpieterse thanks, but I use Excel 2011. — yathrakaaran
@yathrakaaran - just write =HyperLinkText(A1)' in your sheet after adding the module HyperLinkText` which is in answer in the link you mentioned. — Mrig

Mrig Mrig · Accepted Answer · 2016-05-02T14:21:10

In your excel sheet write =HyperLinkText(A1). Give the cell range as required. You'll have to add below code in your module.

This is exactly the same answer from the link you mentioned in your question given by @RonRosenfeld

Option Explicit
Function HyperLinkText(rg As Range)
    Dim sFormula As String, S As String
    Dim L As Long
    Dim H As Hyperlink, HS As Hyperlinks

sFormula = rg.Formula
L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare)

If L > 0 Then
    S = Mid(sFormula, L + 11)
    S = Left(S, InStr(S, """") - 1)
Else
    Set HS = rg.Worksheet.Hyperlinks
    For Each H In HS
        If H.Range = rg Then
            S = H.Address
        End If
    Next H
End If

HyperLinkText = S

End Function

Extracting link texts from Excel Hyperlinks

1 Answers