inverse of HYPERLINK-function
Christian Menzel
I.e. if a cell contains a hyperlink, how can extract the
"link or file location" into a cell?
Example:
cell A1: "http://www.microsoft.com"
cell A2: "Link to Microsoft"
cell A3: =HYPERLINK(A1;A2)
This works fine and cell A3 contains the text "Link to
Microsoft" as a link to the corresponding URL given in A1.
Now I want to do the inverse:
cell A1: contains the text "Link to Microsoft" as Link to
"http://www.microsoft.com"
cell A2: shall contain only the text "Link to Microsoft"
but NOT as link.
cell A3: shall contain only the URL to which to Link in A1
points, i.e. "http://www.microsoft.com"
Which function do I have to write in to cell A3???
I could not find anything suitable.
Best regards
Christian Menzel
David McRitchie
Parsing a HYPERLINK statement is a bit beyond me as it could
contain concatenations and additional formulas, but if it is as
simple as possible with both operands simply enclosed in
double quotes, then I do have a Function that should work.
A1: =HYPERLINK("http://www.abc.com","ABC")
B1: wanted as ABC
=A1
C1: wanted as http://www.abc.com
=URL(A1)
You will find the following Function on my buildtoc.htm page.
Function URL(cell As Range)
'Tom Ogilvy, programming 1999-04-14 Deja: AN=468281862
'Chip Pearson, programming 1999-04-14 Deja: AN=468345917
'David McRitchie, combined 1999-11-13
'cannot process imbedded link to internal sheet yet ...
Application.ScreenUpdating = False
If Trim(cell.Formula) = "" Then
URL = ""
Exit Function
End If
If Left(UCase(cell.Formula), 11) = "=HYPERLINK(" Then
If Left(UCase(cell.Formula), 12) = "=HYPERLINK(""" Then
URL = Mid(cell.Formula, 13, InStr(1, cell.Formula, ",") - 13)
Exit Function 'next part for nonquoted first parm
End If
URL = Mid(cell.Formula, 12, InStr(1, cell.Formula, ",") - 12)
Exit Function
End If
URL = ""
On Error Resume Next
URL = cell.Hyperlinks(1).Address
If URL = 0 Then URL = "'**"
Application.ScreenUpdating = True
End Function
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Christian Menzel" <christia...@icn.siemens.de> wrote in message news:3f1d01c2608c$b460dcd0$39ef2ecf@TKMSFTNGXA08...
David McRitchie
remove the ScreenUpdating lines from the URL() macro, they were not
properly matched and it won't make the function work better.
David McRitchie
What I gave you is what you asked for. In fact you described
as if using the HYPERLINK Worksheet Function which you are
not but it does also pick out the hyperlink from the object type
hyperlinks as you would have found had you tried it, or looked
at that area in the web page I referred you to.. Actually
the following function which could be considered a subset of what
I gave you would do exactly what you asked in your attachment
and nothing else. The attachment was quite unnecessary.
A1: [ 808 ]
hyperlink to: http://www.itu.int/R00-WP8F-C-0808.doc
D1: =URL(A1)
--or--
D1: =HyperlinkAddress(A1)
Install the following function the same as you install a macro,
instructions to install are in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function
The above function and the previously described function (URL), and
even another function that
works for Excel hyperlinks (HyperLinkText by Bill Manville)
can all be found on my page
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"christian Menzel" <christia...@icn.siemens.de> wrote in message news:454701c260bd$f2d3fde0$3bef2ecf@TKMSFTNGXA10...
> Hi David,
>
> thank you for the quick answer.
>
> But I suppose, I did not explain my question clear enough.
> I don't want to "parse" a HYPERLINK-comment. I want to
> split the content of a cell that contains a link to an
> URL, so that the string of URL is displayed in another
> cell.
> As shown in the attached example-file:
> A2 contains a link "[ 808 ]" that point to the
> URL "http://www.itu.int/R00-WP8F-C-0808.doc".
>
> Now I need a (simple) function in colume D, which
> (extracts the URLs of the links in colume A and) displays
> the URLs in colume D.
>
> For cell D2 I extracted the URL manually.
>
> Best regards
> Christian
> >.
> >
Christian Menzel
thanks a lot. That is exactly what I looked for, it is
quite simple and it works!
Christian
Carsten Larsen
Using a function like this one would be a simpler way than
what Richie suggested.
The name you get from a simple reference, the hyperlink
you get by writing =InvHyper(CELL REFERENCE)
Function InvHyper(ByRef CellRef As Range) As String
InvHyper = Range(CellRef.Address).Hyperlinks(1).Address
End Function
NB Returns nothing if no hyperlink
/Carsten
>.
>
David McRitchie
After Christian redefined his problem with **no function involved** for
specifying the hyperlinks, I gave him the simpler version which follows,
and for which he thanked me in the newsgroup (4 days ago). It will return a
null string if there is no hyperlink. Yours will return a $VALUE! -- not very pretty,
and even worse than 0 had you left out the "As String"..
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function
The longer version was based on his original statement and subject
which said he wanted the inverse of the Hyperlink Function,
in the restatement there was no Function involved for the input.
Don't know what this means.
> NB Returns nothing if no hyperlink
HTH, (more detail on my buildtoc.htm web page)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Carsten Larsen" <car...@swissrisk.com> wrote in message news:724701c263b6$a140a040$35ef2ecf@TKMSFTNGXA11...