Hello, quick question about a formula. (example) I have a cell that contains
a Hot Link Title that reads "Yahoo". I want the adjacent cell to read
"http://www.yahoo.com". But I want to accomplish this by Excel formula
because I have about a thousand cells containing Hot-Link Titles. Does
anyone know an Excel Formula to accomplish this?

Thanks!



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

This little User Defined Function will display the underlying URL:

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 the hyperlink is in A1 then in B1 enter:

=hyp(A1)
--
Gary''s Student - gsnu2007k


"Dave" wrote:

Hello, quick question about a formula. (example) I have a cell that contains
a Hot Link Title that reads "Yahoo". I want the adjacent cell to read
"http://www.yahoo.com". But I want to accomplish this by Excel formula
because I have about a thousand cells containing Hot-Link Titles. Does
anyone know an Excel Formula to accomplish this?

Thanks!



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Thank you so much for the assistance. However, I believe that to be visual
basic and it's gone beyond my level of training. Is there a simple formula
out there that will display the url that sits behind the hyperlink in a cell?

Thank you!