Have you ever had an Excel spreadsheet full of web addresses that were not clickable? Pressing F2 and ENTER is good enough when you only have a few, but when you have dozens, hundreds or even thousands, this can become a very time consuming task.

Fortunately there are a couple of approaches which will save you tons of time.

Note that neither of these two methods do any validating to ensure the result is actually a valid hyperlink.

Method #1: Use an Excel formula to create hyperlinks

This first method uses a formula to create a hyperlink enable version of the cell in an adjacent cell:

=Hyperlink(a1, a1)

The first parameter is the cell address that contains the URL and the second refers to the text you want to have appear in the cell. Note that if your URL does not start with "http://", you will need to add it as follows:

=Hyperlink("http://" & a1, a1)

Of course you could create a more complex formula that could automatically detect whether http:// is required but those above are easier to remember.

Method #2: Using VBA to convert text into hyperlinks (advanced)

The second way is to use VBA and then run the macro. When executed, it 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.

' 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 and select Project Explorer.
  • Look for the folder called Microsoft Excel Objects. Right-click on it, select Insert and then Module.
  • 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.

BONUS: Using VBA to remove Hyperlinks

Another macro you might find useful helps you remove the selected hyperlinks, leaving just the text:

' Written by: Michael Milette
' Copyright 2011-2012 TNG Consulting Inc.
' Purpose: Removes the hyperlinks from the selected range leaving the text.
Sub Remove_Hyperlinks()
  Intersect(Selection, ActiveSheet.UsedRange).Hyperlinks.Delete
End Sub

Related Posts: