Extract URLs From Hyperlinked Text Using Macros in Excel 2010

There may be instances when long hyperlinked text has been made pretty by cloaking it with other text. While that may serve some purposes, there may be times when you want to view the URL’s that are behind the hyperlinks. If you ever find yourself in such a situation, you can take advantage of this simple macro that we put together to enable you to extract URLs from hyperlinked text in Excel 2010.

• Tip: If you’d like to learn about how to create macros without writing them from scratch, check out our tutorial on Creating Macros Using the Macro Recorder.

How to Extract URLs From Hyperlinked Text Using Macros

Basic Macro Info

Level of Difficulty : Easy

Works On: Excel 2007, 2010, 2013, 2016

Description : This macro will allow a user to extract the URLs from all hyperlinks in a worksheet.

Code:

Sub Extract_URLs()
Dim Hyli as Hyperlink
For Each Hyli in ActiveSheet.Hyperlinks
Hyli.Range.Offset (0, 2).Value=Hyli.Address
Next
End Sub

How to Use the Extract URLs Macro

1. Copy the code.

2. Open Excel. Press Alt + F11 to enter the Visual Basic Editor. If necessary, press Ctrl + R to show the Project Explorer.

3. Right-click the desired file on left (in bold).

4. Choose Insert -> Module.

5. Paste code into the right pane.

6. Press Alt + Q to close the VBE.

7. Save the workbook in the XLSM format.

8. Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.

9. On the Developer tab, in the Code group, click Macros.

10. In the Macro dialog box, select the macro and click Run.

Sample Workbook with Extract URLs Macro

To test the Extract URLs macro, please download the sample file Extract URLs.xlsm.