Forum Discussion

Cobranack's avatar
Cobranack
Copper Contributor
Jan 21, 2024

Extracting URL from hyperlink and making it automatic

 

Function URL(Hyperlink As Range)
  URL = Hyperlink.Hyperlinks(1).Address
End Function

 

 

Hello,

The VBA code in the first image allows me to use =URL() to extract the URL.


I am trying to make the URL extraction automatic, instead of having to re-drag the formula down every time I want the URL extracted in Column B.

In A2, we have Microsoft, which is hyperlinked to Microsoft.com. However, because I inputted the text first, and then added a hyperlink after, the Column B doesn't get updated. (unless I re-drag the formula down)


How can I make sure Column B automatically extracts the URL - even if I added a hyperlink to a text later?

 

Excel
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Jan 22, 2024

    Cobranack 

    here's an example VBA code that will automatically update the Column B whenever there's a new added hyperlink in column A.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        For Each cell In Target
            If cell.Hyperlinks.Count > 0 Then
                cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
            End If
        Next cell
    End If
End Sub

Resources