title: Extract URL From Excel Hyperlink Formula
body: <p>I have an Excel file with hundreds of cells that use the Hyperlink formula <code>=HYPERLINK( &lt;targetURL&gt;, &lt;friendlyName&gt; )</code>. I need to extract the plain text URLs from these. Most examples that I've found rely on the cell using a different hyperlinking method.</p>
  
  <p>So a function like this:</p>
  
  <pre><code>Function HyperLinkText(pRange As Range) As String
  
     Dim ST1 As String
     Dim ST2 As String
  
     If pRange.Hyperlinks.Count = 0 Then
        HyperLinkText = "not found"
        Exit Function
     End If
  
     ST1 = pRange.Hyperlinks(1).Address
     ST2 = pRange.Hyperlinks(1).SubAddress
  
     If ST2 &lt;&gt; "" Then
        ST1 = "[" &amp; ST1 &amp; "]" &amp; ST2
     End If
  
     HyperLinkText = ST1
  
  End Function
  </code></pre>
  
  <p>results in cell text "not found". Alternatively, is there a way of converting these cells to the other hyperlink format so that the macro I have works?</p>