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.
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 :)
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
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.
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
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.
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
To run the macro:
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
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!
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.
