Forum Discussion
How can I extract URL from hyperlinks?
I have a worksheet where one column contains hyperlinks (like created with =HYPERLINK(link;text)). How can I create a formula extracting link urls from those hyperlinks? I've tried dozen of search qu...
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function below
- Press F5 and click “Run”
- Get out of VBA (Press Alt+Q)
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
https://my-notebook-online-2021.blogspot.com/2021/08/how-to-extract-hyperlink-in-excel.html
WORKS RIGHT AWAY; ALL WE HAVE TO DO IS ADD A COLUMN RIGHT OF THE HYPERLINK.
ATEN USE TEXT TO COLUMN OPTION SEPEARTE AND TAG
agrawalr1 can you provide the specific syntax for that? E. G. If the hyperlink is in A1, what is the formula you would type in B1?
- Thank you. The VBA code worked quite well.
- Created an account to like this, you saved me hours of work with this. Thank you SO much!
datta9381 Thank you, that saved me some time. I just had to add the SubAddress property to get the full address of my links instead of just the domain piece:
HL.Range.Offset(0, 1).value = HL.Address & HL.SubAddress
