18

How do I turn a column of urls into hyperlinks, so that users can click on the url in a cell and have it open in a browser?

I pasted 100 urls and each went into its own cell. When I double-click in the cell and then leave it, Excel turns the text blue and makes a link out of it. I don't want to double-click a hundred times, but still want to format all the cells into links.

1
  • 1
    Totally as an aside, but something to be aware of if hyperlinks don't work as expected: Office uses an odd Microsoft Office Existence Discovery workflow (involving Internet Explorer even if that is not your default browser) when you click such URL.
    – Arjan
    Jun 27, 2010 at 19:36

10 Answers 10

16

The function in Excel for doing a hyperlink is =Hyperlink("http://www.techonthenet.com","Tech on the Net") where "http://www.techonthenet.com" is the internet address and "Tech on the Net" is the title that appears in the Excel cell.

Thus when you are writing the urls into the Excel file just wrap this function around each url. If you don't want to come up with a dynamic name you can always put the url as the name too.

If you aren't inserting the values programmatically then this site mentions using the HYPERLINK worksheet function. Though a even better reference is this which walks you through how to add a macro to excel and they supply the code for the macro. Thus when after you add this macro you can select the column of urls and run the macro and it converts the whole column into hyperlinks

3
  • 1
    i pasted 100 urls and each went into its own cell. this is good but they are plain text. when i dblcick into the cell and then leave it excel tirns the text blue and makes a link out of it. i dont want to dbl click a hundred times and want to format all the cells into links like it is doing for me.
    – kacalapy
    Jun 25, 2010 at 15:11
  • 1
    I added another way for you to do this. I assumed you were doing it through a program you wrote somehow since you are on stackoverflow.
    – Kyra
    Jun 25, 2010 at 15:33
  • Any chance just applying some different formatting might do the trick too? I kind of doubt that Excel will really insert the hyperlink formula when it does its auto-magic. (I don't have Excel here, but pressing Ctrl-1 for the cell properties might show something useful?)
    – Arjan
    Jun 27, 2010 at 20:00
16

From here: Convert URLs to Clickable Links In Excel

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

  • Open your Excel doc
  • Open the macro editor by pressing ALT+F11.
  • In the Tools Menu, left-click View and select Project Explorer.
  • Look for the folder called ‘Modules’, right-click it, select ‘Insert’, then select ‘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 :)
2
  • Bonus points for clear macro instructions. If you have to do this kind of thing on a regular basis, it helps to save the macro somewhere centrally so you can load it into any workbook you need. You can also create a shortcut-key for the macro within the workbook.
    – NateJ
    Jul 25, 2017 at 16:30
  • A great addition to your ribbon in Excel. Highlight fields, click button, Boom! Hyperlinked cells. Jul 28, 2017 at 5:05
5

Hard to believe there isn't an optional setting to tell Excel to treat URLs as live links. After all Outlook automatically does this. But then again - this is a Microsoft product, sigh.

I have a column of links in Excel. I selected the column and pasted it into an email to myself. When I got the mail (Excel column still selected) I pasted the live links back into the column. Done!

Alternatively, you could save the email as a draft and open the saved draft again. There's no need to actually send and receive the email.

1
  • Nice tip! This actually worked with 5000 urls as well, and it still works 9 years later in a totally different version of Outlook. As mentioned, you don't need to send the e-mail, just close and re-open the draft. Jan 26 at 12:59
1

The easy way to do this is simply save the Excel file as an HTML page. Then reopen the HTML page in Excel and the links will be clikable.

UPDATE
Sometimes this does not work. But this seems to always work. Right click selected column URLs are located in. Then Click Hyperlink, then click "Place in this Document" This seems to always work

2
  • Sometimes this does not work. But this seems to always work. Right click selected column URLs are located in. Then Click Hyperlink, then click "Place in this Document" This seems to always work.
    – Richard
    Jul 25, 2013 at 13:43
  • I have added your comment to your answer. You are always welcome to update your answers
    – Shekhar
    Jul 25, 2013 at 14:21
1

The following will create clickable links in one formula

=CONCAT("<",HYPERLINK(M28,M28),">")

If you want to overwrite the formula and clean up the <> then:
Copy and paste over the formula with the value.
use Ctrl+H to replace < and > individually with nothing.

0

Kyra's answer points you here which essentialy gives you this, and was a good direction. Allows you to do a large selection as you request and will convert them all.

Sub addHypers()

For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
    If cell <> "" Then
    ActiveSheet.Hyperlinks.Add cell, "http://" + cell.Value
    End If
    Next cell
End Sub

Sub removeHypers()
Intersect(Selection, ActiveSheet.UsedRange).Hyperlinks.Delete
End Sub
0

See how to convert url text to clickable hyperlink in Excel for easy how-to instructions.

In Excel, click on the column you want to convert to clickable hyperlinks.
Follow the first option: Convert URL text to clickable hyperlink with VBA code

Go through the steps and after you've pressed F5, close the Microsoft Visual Basic application to return to your Excel file. Your URLs will now be clickable!

0

In newer versions of Excel (2018 onwards), just open up the "Styles" dropdown in the Home menu bar and select "hyperlink" as the style. This will format a text hyperlink as a clickable hyperlink for the range you have selected.

1
  • 2
    This only changes the formatting to hyperlink style, but does not make the link clickable.
    – Paul
    May 3, 2020 at 10:14
0

The answer by Niall Flynn above (dated 6-Jan-2012, edited by nixda), is indeed the superior way to convert text to hyperlinks. In Excel 2016, the set up is different. Based on N. Flynn's answer, for the benefit of those who are not familiar with creating macros in VBA, the procedure is as follows:

To create the macro

  • Enter the VBA macro editor via Alt+F11
  • In the View menu, select Project Explorer
  • Right-click the worksheet to be modified (under Microsoft Excel Objects)
  • Select Insert, then select Module
  • Paste in the code provided in N. Flynn's answer
  • Alt+F11 to return to the Excel workbook

To run the macro:

  • Select the text that is to be converted
  • Go to the View menu, select Macros
  • Select View Macros
  • Run the new macro that appears (Convert_To_Hyperlinks)
-1

If you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.

In the new column type in the formula =HYPERLINK(A1) (replacing A1 with whatever cell you are interested in). Then copy the formula down the rest of the entries.

1
  • 1
    This suggestion has already been made by the accepted answer. Could you edit your answer to explain how this is different?
    – Burgi
    Apr 19, 2016 at 10:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .