Pasting a set of URLs?

[Terry Pinnell]

I suspect I'm making heavy weather of this, so would appreciate advice
please.

I have a set of standard URLs in my text editor, like
http://www.xyz.com
http://www.abc.com/123.htm
etc

I paste them into a new Excel 2000 column. But how can I quickly get
them all to become proper links, so that a click will open that
address in my browser please?

So far, the only way I can do it is by using Insert > Hyperlink
(Ctrl+k) on each cell, and then copy/pasting the Text to Display into
the address box. Tedious with a large number to do.

I though I'd solved it by doing the first one that way and then using
the Format Painter on all the others. That turned them all promisingly
blue and underlined - but they had not received the magic clickability
attribute!

Failing any clever method, I suppose a repetitive macro might be the
way to go, if I can remember how to make them ;-)

 

[Guest]

Lets say you have a column of "lazy" hyperlinks (don't respond to clicking)
in column A from A1 thru A100.

In B1 enter:
=HYPERLINK(A1)
and copy down. This will make a nice clickable list.

 

[Terry Pinnell]

I suspect I'm making heavy weather of this, so would appreciate advice
please.

I have a set of standard URLs in my text editor, like
http://www.xyz.com
http://www.abc.com/123.htm
etc

I paste them into a new Excel 2000 column. But how can I quickly get
them all to become proper links, so that a click will open that
address in my browser please?

So far, the only way I can do it is by using Insert > Hyperlink
(Ctrl+k) on each cell, and then copy/pasting the Text to Display into
the address box. Tedious with a large number to do.

I though I'd solved it by doing the first one that way and then using
the Format Painter on all the others. That turned them all promisingly
blue and underlined - but they had not received the magic clickability
attribute!

Failing any clever method, I suppose a repetitive macro might be the
way to go, if I can remember how to make them ;-)

I thought I'd have a crack at the macro approach anyway meanwhile, but
hit a snag. Although I used a Copy (and then a Paste) while recording,
the macro insists on using the same original *contents* every time I
use it on a cell. IOW, it recorded the very first address and that's
obviously now getting pasted every time I use it, even though it
should be copying new contents.

Is there some trick to getting it to generate a simple copy (Ctrl+c)
'operation', without the specific contents please?

 

[David McRitchie]

Hi Terry, (for the macro approach, you asked for)

In Excel 2002 and above:
Tools, AutoCorrect Options, AutoFormat As You Type (tab
[x] Internet and network paths with hyperlinks.
(Would only affect individual cells as typed)

If you reenter the value with F2 then Enter for each it will
become a hyperlink. But for a lot of cells you would use a macro...

macro approach:
Creating Hyperlinks for all Cells in a Selection (#MakeHyperlinks)
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#MakeHyperlinks

To install a macro that you would get from the above
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

More information on Hyperlinks in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm




"Terry Pinnell" wrote...

 

[Don Guillett]

I have often found that xl will oftenautomatically convert to a hyperlink
with a couple of clicks.
tools>options>autocorrect>correct as you type>>
or you could just have the text with a right click or double click event
tied to a follow hyperlink.

 

[Terry Pinnell]

Thanks all, much appreciate the help.

Hi Terry, (for the macro approach, you asked for)

In Excel 2002 and above:
Tools, AutoCorrect Options, AutoFormat As You Type (tab
[x] Internet and network paths with hyperlinks.
(Would only affect individual cells as typed)

If you reenter the value with F2 then Enter for each it will
become a hyperlink.

So it does!

And I found that if I typed the link in directly (e.g.
'www.microsoft.com') then it became a proper link, yet if I pasted it
from my text editor or whatever, it remained mere text.

But for a lot of cells you would use a macro...
macro approach:
Creating Hyperlinks for all Cells in a Selection (#MakeHyperlinks)
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#MakeHyperlinks

The first of those worked fine.

To install a macro that you would get from the above
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

Very glad you included that, David, as it's been so long that I'd
forgotten how non-intuitive the VBA Editor window was! To me anyway.
Faced with this in the Project pane
---------
VBAProject (Photo Upload Index.xls)
-Microsoft Excel Objects
Sheet1 (Sorted by date)
ThisWorkbook
-Modules
Module1