Gfrktyl

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.

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

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 :)

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

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!