I have a column of 3,000 urls in format http://www.... None of these are
actually enabled . I need to be able to click on them and go right to the
website. I also need the actual website address to still show, AND be
clickable. Is there a way to do this without manually going in to the
"insert hyperlink" box and doing all 3000 one at a time?! I have excel 2003.

Assuming your url's are in column A:
In a seperate column, use this formula
=HYPERLINK(A2,A2)
Copy down as needed.

For viewability, I'd then hide the column contain the dead url's.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy1997" wrote:

I have a column of 3,000 urls in format http://www.... None of these are
actually enabled . I need to be able to click on them and go right to the
website. I also need the actual website address to still show, AND be
clickable. Is there a way to do this without manually going in to the
"insert hyperlink" box and doing all 3000 one at a time?! I have excel 2003.

Assuming your urls are in column A:

In B2, input
=HYPERLINK(A2,A2)

Copy down. For viewability, I'd hide the A column so that only the 'live'
urls are visible.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy1997" wrote:

I have a column of 3,000 urls in format http://www.... None of these are
actually enabled . I need to be able to click on them and go right to the
website. I also need the actual website address to still show, AND be
clickable. Is there a way to do this without manually going in to the
"insert hyperlink" box and doing all 3000 one at a time?! I have excel 2003.

Select the 3000 cells then run this macro.

Sub MakeHyperlinks()
'David McRitchie
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With ActiveSheet
.Hyperlinks.Add anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
End With
Next Cell
End Sub


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 11:14:01 -0700, Judy1997
wrote:

I have a column of 3,000 urls in format http://www.... None of these are
actually enabled . I need to be able to click on them and go right to the
website. I also need the actual website address to still show, AND be
clickable. Is there a way to do this without manually going in to the
"insert hyperlink" box and doing all 3000 one at a time?! I have excel 2003.

Luke -- I did this, and it works, but now I can't delete column A, or I get
the !REF error, of course, because A is used in the formula. What can I do
about that? (I can only have one column of the url's, since we have an upload
script to upload the information from the spreadsheet.)

"Luke M" wrote:

Assuming your urls are in column A:

In B2, input
=HYPERLINK(A2,A2)

Copy down. For viewability, I'd hide the A column so that only the 'live'
urls are visible.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy1997" wrote:

I have a column of 3,000 urls in format http://www.... None of these are
actually enabled . I need to be able to click on them and go right to the
website. I also need the actual website address to still show, AND be
clickable. Is there a way to do this without manually going in to the
"insert hyperlink" box and doing all 3000 one at a time?! I have excel 2003.