Change URLS in form exports to hyperlinks
This is an Excel trick that will go through the selected range of cells and turn them all into hyperlinks. Note that this method only works if the workbook is in single user mode, not multi-user shared mode.
Code to copy:
' Written by: Michael Milette ' Copyright 2011-2012 TNG Consulting Inc. ' Purpose: Converts the selected text into hyperlinks. ' Note: HTTP is assumed if not specified in the text. Public Sub Text_To_Hyperlink() Dim Cell As Range For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Trim(Cell) > "" Then If Left(Trim(Cell), 4) = "http" Then ' handles http and https ActiveSheet.Hyperlinks.Add Cell, Trim(Cell.Value) Else ' Default to http if no protocol was specified. ActiveSheet.Hyperlinks.Add Cell, "http://" & Trim(Cell.Value) End If End If Next End Sub
Creating the Macro
- Open your Excel workbook
- Open the macro editor by pressing ALT+F11
- In the View select Project Explorer
- Look for the folder calledMicrosoft Excel Objects. Right-click on it, selectInsertand thenModule.
- Paste the above code into the project module you have selected.
- Press ALT+F11 to return to your Excel workbook or click on its button in the Windows taskbar.
A good place to put such macros is actually in your personal workbook so that it is available regardless of the workbook you are editing. You can Google that to find out how.
To run the Macro
- To execute the macro, select the 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 should now be clickable.