Excel - Extract Actual Addresses From Hyperlinks With VBA Code

Extract Actual Addresses From Hyperlinks With VBA Code

For a lot of hyperlinks, the above method will be tedious and time consuming, so the following VBA code can help you quickly extract multiple addresses from the hyperlinks at once.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Extracthyperlinks()
'Updateby20140318
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, 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
3. Then press F5 key to run the code, and a dialog pops out for you to select the hyperlinks that you want to extract the real addresses, see screenshot:
4. Then click OK, and the selected cell contents have been converted to the real hyperlink addresses in the original range. See screenshots:


Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.
    Blogger Comment

0 comments:

Post a Comment