Monday, July 22, 2013

Excel Tip : Extract URL's from Hyperlink list

Problem : Extracting URLs from a long list of hyperlinks.

Solution : Using VBA module

Steps :

  • Open up a new workbook.
  • Get into VBA (Press Alt+F11)
  • Insert a new module (Insert > Module)
  • Copy and Paste the Excel user defined function below

  1. Function GetURL(cell As range,Optional default_value As Variant)
  2. 'Lists the Hyperlink Address for a Given Cell
    'If cell does not contain a hyperlink, return default_value
    If (cell.range("A1").Hyperlinks.Count <> 1) Then
       GetURL = default_value
       GetURL = cell.range("A1").Hyperlinks(1).Address
    End If
    End Function

  • Get out of VBA (Press Alt+Q)
  • Use this syntax for this custom Excel function: =GetURL(cell,[default_value]

  • Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

    0 comments : on " Excel Tip : Extract URL's from Hyperlink list "