Convert URLs to Clickable Links In Excel

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
End If
Next
End Sub

Creating The Macro

  1. Open your Excel doc
  2. Open the macro editor by pressing ALT+F11.
  3. In the Tools Menu, left-click View and select Project Explorer.
    View -> Project Explorer (CTRL+R)
  4. Right-click the Project you are working on.
  5. Right-click ‘Modules’, select ‘Insert’, then select ‘Module’.
    Modules -> Insert -> Module
  6. Paste the code into the project module you have selected.
  7. Press ALT+F11 to return to your Excel workbook (or click on its icon in the Windows taskbar).

Run The Macro

  1. To execute the macro, select the unclickable text links you want to convert to clickable hyperlinks.
  2. Press ALT+F8 to open the Macro selector window and click on the macro you just created.
  3. Your Links are now all Clickable! Saving you time and data entry fatigue :)

Useful Excel Links

Comments

  1. PCLicious Video Tutorials
    Thu 12th Jan 2012 at 4:59 am

    I am an Excel Aficionado, so I can appreciate this Excel knowledge.

    Here is a code to remove all hyperlinks in a Excel spreadsheet including email addresses, thought it may be cool to provide the opposite of what you have done:

    Sub ZapHyperlinks()
    Cells.Hyperlinks.Delete
    End Sub

    Thanks again for the knowledge :)

    Reply
    • Niall Flynn  –  Fri 20th Jan 2012 at 10:35 am

      This can also be done with right click, it is a lot more difficult to add the hyperlinks. If anyone has a test to see if the link is live now that would be a nice piece of software :)

      Reply
  2. Melafont
    Mon 20th Feb 2012 at 8:08 pm

    This procedure converts the cell to a hyperlink that in Excel 2010 does not work, comes up with messge “Cannot open the specified file”.

    Reply
    • Niall Flynn  –  Wed 22nd Feb 2012 at 9:10 am

      Strange, it seems to work fine for me, use it every day in excel 2010, email me hello@niallflynn.ie if you need any help or want to send me a file. Thanks for the comment :)

      Reply
  3. Stuart
    Wed 28th Mar 2012 at 8:00 pm

    Why do you check that Cell is equal to the empty string? It doesn’t seem to matter what you check it against it sill creates a hyperlink. i.e. Cell “asdfaf” has no effect. I’d like to prevent the macro from creating hyperlinks on directory listings (i.e. /home/joe/) but do create links for http://domain.com/index.html. I don’t do VBscript so I have no clue what’s really going on here.

    Reply
    • Eric  –  Thu 26th Apr 2012 at 6:22 pm

      Stuart – just change the code: If Cell “” Then

      to this below:

      If left(Cell,4)=”http” Then

      This should only alter those that are ‘http’ links.

      Reply

Add comment