Function to retrieve embedded URL link?

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top