Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 27 '10 at 19:14

This question came from our site for professional and enthusiast programmers.

    
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 '10 at 19:36

6 Answers 6

up vote 9 down vote accepted

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

share|improve this answer
    
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 '10 at 15:11
    
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 '10 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 '10 at 20:00

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
share|improve this answer

Handy Article 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 :)
share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
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 '13 at 13:43
    
I have added your comment to your answer. You are always welcome to update your answers –  Shekhar Jul 25 '13 at 14:21

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.