Hi All, I have a column in excel, wherein I have all the website url values. My question is I want to turn the url values to active links. There are about 200 entries in that column with different urls in all cells. Is there a way I can create active hyperlinks to all the cells without writing a macro.
+1
A:
If you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.
In the new column type in the formula =HYPERLINK(A1) (replacing A1 with whatever cell you are interested in). Then copy the formula down the rest of the 200 entries.
hawbsl
2010-04-07 20:47:05
If my column A contains 200 link values, is it possible to write a formula that would hyperlink the 200 values and rewrite it in column A, instead of creating a new column?
developer
2010-04-07 20:50:41
+1
A:
OK, here's a hokey solution, but I just can't figure out how to get Excel to evaluate a column of URLs as hyperlinks in bulk.
- Create a formula, ="=hyperlink(""" & A1 & """)"
- Drag down
- Copy new formula column
- Paste Special Values-only over the original column
- Highlight column, click CTRL-H (to replace), finding and replacing = with = (somehow forces re-evaluation of cells).
- Cells should now be clickable as hyperlinks. If you want the blue/underline style, then just highlight all cells and choose the Hyperlink style.
The hyperlink style alone won't convert to clickable links, and the "Insert Hyperlink" dialog can't seem to use the text as the address for a bunch of cells in bulk. Aside from that, F2 & Entering through all cells would do it, but that's tedious for a lot of cells.
Indolent Code Monkey
2010-04-07 20:47:56