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:
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:
Ready? Click OK and the script will replace the original cells content with the hyperlinks:
This will save you a lot of time!