Function to retrieve embedded URL link?
 

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

Function to retrieve embedded URL link?
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Scott Bass" wrote:

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

Function to retrieve embedded URL link?
 

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




Barb Reinhardt wrote:
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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email


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