Excel: Easily convert hyperlinks to text showing the URL address

So there were about 5000 rows in the spreadsheet I was working with and each row had a hyperlink labelled "Website".
This made life a little difficult when I converted the file to CSV for processing because the hyperlink was removed...
So before converting to CSV, I converted it into the actual URL location/address/href/source using a neat little macro.
  • In your spreadsheet, select the cells that you want to convert
  • Press ALT+F8 to bring up the Macro dialog below
  • Type in "RemoveHyperlinks" and click "Create"
image
  • It should open up in a new window
  • Between "Sub" and "End Sub", paste the following code
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
    If Cell.Hyperlinks.Count > 0 Then
        Cell.Value = Cell.Hyperlinks.Item(1).Address
    End If
Next
  • It should now look like this:
image
  • Press F5 (or click the "Play" button)
  • The hyperlinks should now display the actual URL

Sources

A few scrambled pieces of this and this.
 
Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog