Lookup Hyperlink
 

Q.1) The VLOOKUP only allows copying of lookuped text specified by VLOOKUP
(lookup_value, table_array, col_index_num, range_lookup) which is in text
format ;
however, if we want to get the hyperlink assigned to each of the text, how
do we do that?

Q.2) Apart from doing it manually, how to extract the hyperlink of a column
into a extra column and appears in text format (e.g.
http://www.microsoft.com) ?

Lookup Hyperlink
 

Hi Tommy,

Please give us a few details - Q2. are you saying that you have
Http://www.microsoft.com in cell A1 as a hyperlink and you want
Http://www.microsoft.com in cell B1 as text? If so the formula =A1 in cell
B1 returns the text of the hyperlink. To do the first one, if I understand
correctly, you probably will need to create your own VBA function, I don't
believe there are any built-in spreadsheet functions that can do this.

Shane Devenshire
Microsoft Excel MVP

"Tommy" wrote in message
...
Q.1) The VLOOKUP only allows copying of lookuped text specified by VLOOKUP
(lookup_value, table_array, col_index_num, range_lookup) which is in text
format ;
however, if we want to get the hyperlink assigned to each of the text, how
do we do that?

Q.2) Apart from doing it manually, how to extract the hyperlink of a
column
into a extra column and appears in text format (e.g.
http://www.microsoft.com) ?

Lookup Hyperlink
 

The first question is easy. If you have a formula like:

=VLOOKUP(A1,G1:H100,2) that returns a "cold" hyperlink, replace it with:
=HYPERLINK(VLOOKUP(A1,G1:H100,2))

The second question involves the use of this UDF:

Public Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

So if A1 contains an Inserted hyperlink or a formula like:

=HYPERLINK("http://www.cnn.com","news")

=hyp(A1) will display the URL

http://www.cnn.com
--
Gary''s Student - gsnu200788


"Tommy" wrote:

Q.1) The VLOOKUP only allows copying of lookuped text specified by VLOOKUP
(lookup_value, table_array, col_index_num, range_lookup) which is in text
format ;
however, if we want to get the hyperlink assigned to each of the text, how
do we do that?

Q.2) Apart from doing it manually, how to extract the hyperlink of a column
into a extra column and appears in text format (e.g.
http://www.microsoft.com) ?

Lookup Hyperlink
 

Thanks GS,
is there any way to retrieve the entire cell contents + its hyperlink in
excel? (using vlookup)?

using the HYPERLINK(VLOOKUP(A1,G1:H100,2)) will return the text as a
hyperlink, but not the text + hyperlink associated with it.

i.e. HYPERLINK(VLOOKUP(A1,G1:H100,2)) will return the cell with the word
"label" on it as a hyperlink but not the "c:\label.jpg" link that is attached
to it.

any help would be great!

Thanks!


"Gary''s Student" wrote:

The first question is easy. If you have a formula like:

=VLOOKUP(A1,G1:H100,2) that returns a "cold" hyperlink, replace it with:
=HYPERLINK(VLOOKUP(A1,G1:H100,2))

The second question involves the use of this UDF:

Public Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

So if A1 contains an Inserted hyperlink or a formula like:

=HYPERLINK("http://www.cnn.com","news")

=hyp(A1) will display the URL

http://www.cnn.com
--
Gary''s Student - gsnu200788


"Tommy" wrote:

Q.1) The VLOOKUP only allows copying of lookuped text specified by VLOOKUP
(lookup_value, table_array, col_index_num, range_lookup) which is in text
format ;
however, if we want to get the hyperlink assigned to each of the text, how
do we do that?

Q.2) Apart from doing it manually, how to extract the hyperlink of a column
into a extra column and appears in text format (e.g.
http://www.microsoft.com) ?