General discussion

Locked

Excel 2002

By phil.meredith ·
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

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by davidfranks78 In reply to Excel 2002

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

Collapse -

by davidfranks78 In reply to Excel 2002

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.

Collapse -

by davidfranks78 In reply to

Note: Replace "http: with http: and .com" with .com

Collapse -

by davidfranks78 In reply to

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.

Collapse -

by ReWrite In reply to Excel 2002

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 "httpwww.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

Collapse -

by phil.meredith In reply to

Great answer - got it!! It has saved me hrs and hrs

Collapse -

by chitosunday In reply to Excel 2002

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

Collapse -

by phil.meredith In reply to Excel 2002

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums