Please Note: This article is written for users of the following Microsoft Excel versions: 97, 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: Extracting URLs from Hyperlinked Images.

Extracting URLs from Hyperlinked Images

by Allen Wyatt
(last updated September 7, 2013)

One way you can use data from the Internet in an Excel worksheet is to copy it from a Web page and then paste it into the worksheet. For instance, you can select a table of data on a Web page, press Ctrl+C to copy it to the Clipboard, select a cell in Excel, and then press Ctrl+V. Excel does its best to parse the data and put it in the proper cells, just like it was in the original table.

The problem is that you'll often get more than just the table data. If there were other objects in the data you copied from the Web, those objects are also pasted into the worksheet. It is not uncommon to end up with all sorts of small graphics in the worksheet. If these graphics were originally hyperlinks, you may want to actually extract the hyperlink and then delete the graphic. This would make the data in the worksheet much more usable.

The way to do this is with a macro. Once you've pasted the Web information into the worksheet, run the following macro.

Sub ConvertHLShapes()
    Dim shp As Shape
    Dim sTemp As String

    For Each shp In ActiveSheet.Shapes
        sTemp = ""
        On Error Resume Next 'go to next shape if no hyperlink
        sTemp = shp.Hyperlink.Address
        On Error GoTo 0
        If sTemp <> "" Then
            shp.TopLeftCell.Value = sTemp
            shp.Delete
        End If
    Next
End Sub

This macro steps through each of the shapes in the worksheet. It then checks to see if the shape has an associated hyperlink. If it does, then the address of that hyperlink (in the sTemp variable) is placed into the cell at the top-left corner of where the shape is located. The macro deletes any shapes that have hyperlinks; you can force it to delete all shapes in the worksheet by simply moving the shp.Delete line to the outside of the If ... End If structure.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2907) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Extracting URLs from Hyperlinked Images.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Problems Using Words as Bullets

If you know the secret, you can use actual words as "bullets" in a bulleted list. The built-in bulleted lists in Word aren't ...

Discover More

Arranging Workbook Windows

If you find yourself working with a number of different workbooks at the same time, you may want to arrange your desktop so ...

Discover More

Entering Calculations in a Form Field

One of the many uses for Word is to create forms that can be easily filled in by other people. This is made possible by the ...

Discover More

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!

MORE EXCELTIPS (MENU)

Extracting Hyperlink Information

In Excel, a hyperlink consists of two parts: the text displayed for the link and the target of the link. You can use a macro ...

Discover More

Deleting a Hyperlink

Hyperlinks can be helpful in some worksheets but bothersome in others. Here's how to get rid of any hyperlinks you don't ...

Discover More

Removing Hyperlinks without a Macro

If you have a whole slew of hyperlinks in a worksheet and you want to get rid of them, it's easier than you think. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share