13

I have a list of links in an Excel spreadsheet that I want to turn into hyperlinks so I can view them in a web browser. Is there a way I can change all of them to hyperlinks at once? I'm using Excel 2013. Thanks for your help!

  • Excel automatically creates hyperlinks when a cell entry begins with http://, www., ftp://, mailto:, file://, news: or \\. So, unless the feature was turned off, I'd bet the hyperlinks are invalid. This means they may not work if converted as is. Can you give us an example of the hyperlinks you are trying to convert? – CharlieRB Apr 9 '13 at 17:27
  • 2
13

No need to do any programming or go to another app to fix this.

TEXT link to Clickable Hyperlink in EXCEL

Add a New Column next to your column of text links

If your text link is www.example.com (no http://):

  • Paste this formula into the cell of your new column next to your original column of TEXT that you want to change to a clickable hyperlink.

    =HYPERLINK("http://"&A2)

    The last two characters before the close parens is the target cell / whatever column and row has the text link

    You can copy this formula down the NEW column and you are all set

If you text link is http://www.example.com:

  • Add the new column

    If the http:// is already in the text itself Just past this into the cell next to your text link and it should work

    =HYPERLINK(A2)

    The last two characters before the close parens is the target cell / whatever column and row has the text link

    You can copy this formula down the NEW column and you are all set

5

I have used these steps myself.

Select the offending column of links On the Home tab ribbon in Excel, select Clear > Clear Hyperlinks

Observe that the links are now removed in the column. If you click any cell twice in column H, the link will reset to the correct location and become active.

It would take forever to click each cell twice to make all the links active, so try this instead. Press Alt + F11 on your keyboard. This will bring up a Microsoft Visual Basic window. On the top Nav bar in Visual Basic, select Insert > Module

Copy and paste this code into the code window of the module. It will automatically name itself HyperAdd:

Sub HyperAdd() 

    'Converts each text hyperlink selected into a working hyperlink

    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    Next xCell 
End Sub 

Press Alt + F11 on your keyboard to close the Visual Basic editor. In the spreadsheet, select ONLY the cells you wish to be changed to clickable links in the column. If you select the whole column, this can overwhelm Excel and cause the file to crash.

Press Alt + F8. This will bring up a Macro Window. HyperAdd will already be selected. Hit Run

You’re done. Test your links. Each link should now be active.

2

You want the HYPERLINK() function.

http://office.microsoft.com/en-us/excel-help/hyperlink-function-HP010342583.aspx

It will take a a cell with a url and convert it to a clickable link. This will allow you to CLICK THE LINKS WHILE STILL IN EXCEL. If you need to create a HTML file that you browse in IE or FF as a big list of links, use Brad's answer.

1

I had same issue and in poking around, I found a quick workaround that actually gets the job done without any macros, etc.

In the first cell of the column that has the URL's in it (i.e. the first URL, not the title) and double click on the URL in Cell to 'activate' it. Wait till it turns into a link (you may have to actually open the item so the sheet recognizes it.

Once it is underlined, you simply click the next cell down and select to the end of the column data. NOW the Cell Styles will work. Click Cell Styles from the Home Tab and select Hyperlink and viola - all the cells change to clickable hyperlinks.

May not be pretty, but it works!

0

You don't specify in what form the links are in but assuming they are fully qualified (eg. http://www.google.com). Then you should be able to save the Worksheet as a Web Page. If you limit the Save to the selected Sheet this will produce a single HTML file with the links as clickable elements.

Links

NOTE: I have only tested this on Excel 2010 right now.

0

Make alle cells in a defined row to hyperlinks using VBA

Sub MakeHyperlinks()
    Dim lngRow As Long
    Dim lngCol As Long

    lngRow = 1 ' start first row
    lngCol = 2 ' look for cells in second col

    With Sheets("Sheet1")
        Do Until IsEmpty(.Cells(lngRow, lngCol)) = True ' stop when found the first empty cell
            .Hyperlinks.Add Anchor:=.Cells(lngRow, lngCol), Address:=.Cells(lngRow, lngCol)
            lngRow = lngRow + 1
        Loop

    End With
End Sub
0

Style section of the ribbon bar

If you look in the style section of on the home tab, you can select the column and click Hyperlink. That will hyperlink all of them

  • This just makes it blue and underlined - it doesn't make it a hyperlink that you can click. – teedyay Sep 27 '17 at 8:46
0

I've been fighting with this issue all evening, and after quite a bit of poking around I finally found a solution that does not require writing a macro, or excessive amounts of clicking. (My particular problem required me to convert 3000+ cells to hyperlinks)

  • Start by Right clicking on the ribbon and select "Customize the Ribbon".
  • Under the "Choose commands from" menu, select "Commands Not in the Ribbon".
  • In the list of commands you are given you should find a command for "Paste as Hyperlink".
  • Add that command to your ribbon somewhere.
  • Select all the cells you wish to convert to hyperlinks and copy them to the clipboard.
  • Press the "Paste as Hyperlink" button that is now on your ribbon.

Congratulations, you're done!

  • My apologies. I was clearly tired when I posted this last night. After going back to the spreadsheet I was creating links in I discovered that all of the cells I used this method to create a hyperlink with were not linked to the external URL, but instead linked to a cell within the spreadsheet. – David Ellis Sep 6 at 20:04

protected by Community Oct 9 at 0:04

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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