Function to retrieve embedded URL link?

  • Thread starter Thread starter Scott Bass
  • Start date Start date
S

Scott Bass

Hi,

I have an Excel worksheet (actually it's a colleague's file) with columns
containing URL's. The anchor text is different than the URL.

For example:

Report A <http://long_convoluted_url_to_sharepoint_site>. The text is
"Report A", blue, underlined, a link, the usual stuff. Cntl-click launches
the browser to the appropriate Sharepoint URL.

The number of columns containing URL's vary from 1 - 5 columns (A - E).
Most times it's just column A, with missing data in the other columns.

Does anyone know of an Excel formula or VB code that could copy the URL link
into another column(s)?

Thanks,
Scott
 
I found this post of Bernard's that answers your question

15-Jan-08 10:57:19

In A1 I entered this formula: =HYPERLINK("www.abc.ca","click for abc")
In B1 I use =GETLINK(A1) and this returned: www.abc.ca
The UDF is
Function getlink(mycell)
myform = mycell.Formula
If Mid(myform, 2, 9) = "HYPERLINK" Then
commapos = InStr(1, myform, ",")
getlink = Mid(myform, 13, commapos - 14)
Else
getlink = ""
End If
End Function

best wishes
 
Back
Top