Extract URL From Excel Hyperlink Formula

by

Here is a method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.

If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet.

The formula will return nothing if there is no hyperlink in cell_ref; change to suit.

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

More Related Contents:

  1. Create hyperlink to another sheet
  2. Format Cell Contents
  3. Excel formula checklist in employees
  4. How do I put double quotes in a string in vba?
  5. Excel VBA – How to add dynamic array formula
  6. How can I URL encode a string in Excel VBA?
  7. Compare double in VBA precision problem
  8. Row count on the Filtered data
  9. Excel VBA Macro: User Defined Type Not Defined
  10. Automate IE via Excel to fill in a dropdown and continue
  11. VBA – Write all possible combinations of 4 columns of data [duplicate]
  12. Remove duplicates from array using VBA
  13. How to automatically insert a blank row after a group of data
  14. Get File Path (ends with folder)
  15. Get length of array?
  16. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  17. Email a single attachment from folder of files each to a different person
  18. Bad data selection using VBScript , on date field values
  19. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  20. Sending object array to variant works before August 2019 Windows update, but fails after update
  21. Find last row in range
  22. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  23. Differences between Excel’s Form Controls & ActiveX Controls
  24. Excel VBA User Defined Function that counts cells with conditional formatting
  25. Excel is calculating a formula with a VBA function as an error unless it is re-entered
  26. SAP Extract into EXCEL via VBA out of memory
  27. reading entire text file using vba
  28. Convert month name into number
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment