09 Dec Convert URLs to Links
Posted at 15:04h in News
So here is the scenario, you have an Excel doc full of plain text links and you need to convert them to clickable links. Normally I would have to click on each cell and hit Enter, this can be very time consuming. Using a small macro you can automate this task. For any of you that have not used Macros before they can take many forms, but in this case it is a few lines of code you can trigger on a selected set of cells.
The Macro Code – Convert_To_Hyperlinks()
Public Sub Convert_To_Hyperlinks()
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" Then
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
Creating The Macro
- Open your Excel doc
- Open the macro editor by pressing ALT+F11.
- In the Tools Menu, left-click View and select Project Explorer.
View -> Project Explorer (CTRL+R)
- Right-click the Project you are working on.
- Right-click ‘Modules’, select ‘Insert’, then select ‘Module’.
Modules -> Insert -> Module
- Paste the code into the project module you have selected.
- Press ALT+F11 to return to your Excel workbook (or click on its icon in the Windows taskbar).
Run The Macro
- To execute the macro, select the unclickable text links you want to convert to clickable hyperlinks.
- Press ALT+F8 to open the Macro selector window and click on the macro you just created.
- Your Links are now all Clickable! Saving you time and data entry fatigue 🙂
Useful Excel Links
- For more general Excel lessons, tips and tricks check out office.com
- Macro info from about.com