Convert 3,000 url's to enabled hyperlinked automatically?
 

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.

Convert 3,000 url's to enabled hyperlinked automatically?
 

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.

Convert 3,000 url's to enabled hyperlinked automatically?
 

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.

Convert 3,000 url's to enabled hyperlinked automatically?
 

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.

Convert 3,000 url's to enabled hyperlinked automatically?
 

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.