Please Note: This article is written for users of the following Microsoft Excel versions: 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Converting a Range of URLs to Hyperlinks.
John has a workbook that has well over a thousands URLs in it, all in column A. These are not hyperlinks; they are straight text of individual URLs. John wants to convert the URLs to active hyperlinks, but doing the conversion individually is extremely tedious, especially for that many URLs.
As is the case with most tedium in Excel, the solution is to use a macro to do the conversion. To be effective, the macro would need to step through each cell in a selected range and, if the cell is not blank, convert the contents to a hyperlink. The following will do the trick:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Sub URL_List()
For Each cell In Selection
If cell.Value <> "" Then
If Left(cell.Value, 7) = "http://" Then
URL = cell.Value
Else
URL = "http://" + cell.Value
End If
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:=URL, TextToDisplay:=cell.Value
End If
Next cell
End Sub
The macro is not foolproof; it assumes that if a cell contains anything at all it is a valid URL. What it does is to check the cell contents and, if the contents aren't prefaced by the "http://" text, then it is added. The hyperlink is then created based on the cell contents.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3110) applies to Microsoft Excel 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Converting a Range of URLs to Hyperlinks.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to get rid of all the hyperlinks in a worksheet? It's easy when you use this single-line macro.
Discover MoreIf you want people to know something about a hyperlink you added to your worksheet, one way to help them is to use ...
Discover MoreIn putting together a workbook, you may develop a worksheet that acts as an index, to contain links that lead to other areas ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-11-06 10:51:58
Andreas
Thanks, worked like a charm!
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments