Forum Discussion
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?
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
