Microsoft Office
09 Fri
10 COM

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

Category : Random News

10 Comments

  1. PCLicious Video Tutorials
    January 12, 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
      January 20, 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
    February 20, 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
      February 22, 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
    March 28, 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
      April 26, 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
  4. Niall Flynn
    July 27, 2012 at 10:47 am

    Very True there is also this option;

    =Hyperlink("http://www.example.com","Title here")

    Check out http://superuser.com/questions/157414/how-to-turn-hundreds-of-text-urls-in-excel-into-clickable-hyperlinks

    Reply
  5. Princess
    November 29, 2012 at 1:37 pm

    Thank you so much!!!!!!!!! Tried another tutorial and it didn’t work. You are the bomb!! lol

    Reply
  6. Niall Flynn
    November 29, 2012 at 1:48 pm

    Why thank you compliments will get you everywhere :) You can check out Office.com for more, I used to work for them and I know they have some excellent Excel training/support.

    Reply
  7. Jerry Swiatek
    March 13, 2013 at 3:12 am

    I know this post is a bit old, but I’m hoping someone can help me. I archive several Twitter chats and I’d love to convert the URLs in my spreadsheet to clickable links. The macro above works well, but my links show up in different places in each cell. Is there code I can use to do the following:

    If column C contains “http”, anywhere in the cell, convert the address to a clickable link.

    Thanks for your help.

    Reply

Leave a comment