Discussion Locked

Excel 2002

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am populating an excel spreadsheet with one column ('ooo's of rows) which contains a URL in the format "http:/xxxxxxxx.com" (including the ")-by importing data from a txt file.

How can I quickly change all the rows into a hyperlink. I currently do it by double clicking each cell, row by row, and it becomes a hyperlink, but it takes hours!

help someone

8 replies

by davidfranks78

Hi
If your problem is to make a range of cells point to a single hyperlink, then this is the solution:

Select the range of cells
Hold down the keys "CTRL" and "K", and you get the hyperlink dialog box.
Type in or browse to the desired hyperlink and hit OK.

All the selected cells will now point to the same hyperlink

Hope this helps

by davidfranks78

Sorry Phil!!! my last answer was all wrong. I didn't understand your question right.

OK this is what you need to do:

Hold down "CTRL" and "F" to bring up the Find dialog box.
Click the "Replace" button
In the "Find What" text box type ""http:" (yes include the quotation mark before the "http")
In the "Replace" text box type "http:" (without the quotation marks
Click the "Replace All" button

Repeat the steps for replacing ".com"" with ".com"

Hope this helps.

by davidfranks78

Sorry Phil once again. This time I really tried the solution:

Instead of double clicking, just press the F2 key and then hit return. This will make the text into a hyperlink and focus will move into the cell below.

by rewrite

Here's what I would do:

1-import your text file (I'm assuming that your imported test of hyperlinks are in column a starting at row 1).
2-Go to cell b1 and type the formula "=hyperlink(a1)" (no quotes) and Enter.
3-Go back to cell b1 and do a copy on cell b1 (hit ctrl-c).
4-Click on the column b header to select the entire column.
5-Do a paste (hit ctrl-v).

This formula should convert all of the text entries in column a to hyperlinks in column b.

I'm not sure if it was a typo but "http:/www.somewebsite.com" is missing the double slash for hyperlinks. It should be "http://www.somewebsite.com". Even so, this formula will convert either entry to a hyperlink.

Cheers.

RW

by chitosunday

Use copy paste by right clicking the cell to copy, choose copy then go the the first destination cell, press the ctrl then click all the rows you want to paste then release the ctrl , then paste it

This discussion is closed to new replies.