Function to retrieve embedded URL link?

[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

 

[Barb Reinhardt]

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

 

[Charlotte E.]

This little function will extract all URLs - no just those made by formula:

http://www.EXCELGAARD.dk/Lib/GETURL/

....and the function has a few 'extra options' as well



CE