الخميس، 30 يوليو، 2015

How to Extract URL from Hyperlink in Excel

Microsoft Excel has a built in function called HYPERLINK() which allows you to convert a link location (URL) and friendly name (anchor text) into a hyperlinked cell that you can click on to open the link in a web browser window. However, Excel does not have a default function that allows you to do the reverse of that—to extract the URL from a hyperlinked cell. In order to do this, we need to get our hands dirty with some VBA (Visual Basic for Applications) code. Don’t worry, it isn’t too complicated.
  1. Hold down Alt + F11 Key to Open the Microsoft Visual Basic for Applications window
  2. Go to Insert -> Module
  3. Paste the following into the Module Window:
    Function GetURL(pWorkRng As Range) As String
    GetURL = pWorkRng.Hyperlinks(1).Address
    End Function
  4. Now use the function GetURL(A1) to extract the URL from the hyperlinked cell.

Good Luck ;)

 

ليست هناك تعليقات:

إرسال تعليق