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.
That was awesome. Thank you so much.
Easy to understand, worked like a charm, very helpful. Thank you very much!
Thorsten