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.
Note:
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off ...
Discover MoreNeed a quick link within a document to some external data? You can paste information so that Excel treats it just like a ...
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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2024 Sharon Parq Associates, Inc.
Comments