|
Pasting a set of URLs?
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 ;-) -- Terry, West Sussex, UK |
Pasting a set of URLs?
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. -- Gary''s Student - gsnu200720 "Terry Pinnell" wrote: 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 ;-) -- Terry, West Sussex, UK |
Pasting a set of URLs?
Terry Pinnell wrote:
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? -- Terry, West Sussex, UK -- Terry, West Sussex, UK |
Pasting a set of URLs?
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...MakeHyperlinks To install a macro that you would get from the above http://www.mvps.org/dmcritchie/excel....htm#havemacro More information on Hyperlinks in http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Terry Pinnell" wrote... 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? |
Pasting a set of URLs?
Thanks all, much appreciate the help.
"David McRitchie" wrote: 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...MakeHyperlinks The first of those worked fine. To install a macro that you would get from the above http://www.mvps.org/dmcritchie/excel....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 --------- ....it was not at all obvious to me either which one of those three (if any) was already selected, nor which one I should select! More information on Hyperlinks in http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Terry Pinnell" wrote... 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? |
| All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com