How to extract a URL from a hyperlinked image, graphic, or icon on Excel

When you copy and paste data from the Web onto an Excel spreadsheet, you sometimes end up copying and pasting images, graphics, or icons that were originally hyperlinks. To make the data usable, you may want to extract just the hyperlink and get rid of the image, graphic, or icon.

You have two options. First is to do it manually:

  1. Right-click a hyperlinked image, graphic, or icon.
  2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
  3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
  4. Press Esc to close the Edit Hyperlink dialog box.
  5. Paste the URL into any cell desired.

But what if you have more than just a few hyperlinked images, graphics, or icons? If you had to do this for each and every single hyperlinked image, graphic, or icon, this can get tedious very very quickly. So the second option is to get the URLs using a macro.

The following example can be useful when extracting hyperlinks from images, graphics, or icons that have been copied into Excel.

Extracting a URL from a hyperlinked image, graphic, or icon on Excel is easy!

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)

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

Looking to extract a URL from a simple hyperlinked text? View steps to extract a URL from a hyperlink »

This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*