Excel knowledge – how to extract the address from a hyperlink?

You have an Excel file that contains many hyperlinks and you want to extract the addresses?

You can do it by hand line for line (mark the cell, right-click, Hyperlink, copy/paste it) if you have to do it for a small number of links.

But if you have to extract a lot of addresses I recommend you to use a Visual Basic (VB) script for this:

How to extract the address from a hyperlink using a script:

In the Excel containing the links press ALT + F11 to open “Microsoft Visual Basic for Applications”. In the menu select “Insert” and “Module”.

Insert the following code into the newly opened window:

insert-vb-code

Sub Extracthyperlinks()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox(“Range”, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Hyperlinks.Count > 0 Then
Rng.Value = Rng.Hyperlinks.Item(1).Address
End If
Next
End Sub

Now execute the script pressing F5. A dialog box will open where you can select the cells containing the links:

select-range

Ready? Click OK and the script will replace the original cells content with the hyperlinks:

excel-results

This will save you a lot of time!

Leave a Comment

Your email address will not be published. Required fields are marked *