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.

Source: https://www.extendoffice.com/documents/excel/1177-excel-extract-hyperlink-from-cell.html

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.