Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Extracting URL from hyperlink and making it automatic

Copper Contributor

 

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

 

Extract URL automatic.PNG

 

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?

 

1 Reply

@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