Links in cell

[mdass79]

hello all,

please refer to the attachment. my question is regarding excel 2003.

i create a new workbook, type some data into the list, then save the file. a few days later, when i open the file again, i find that there are some hyperlinks inserted into some cells in the workbook ( i've highlighted some of the cells in the attachment), which i've never inserted.

my question is - how did the hyperlinks get there, automatically. i do visit the website occasionally (ie. www.seaseacher.com) and how to delete the hyperlinks without destroying data in the cell.

your valuable feedback is most welcome. thanks for your time.

 

[The Villan]

I have stripped the spreadsheet down and have uploaded the revised file.

You may well have copied something in that website into windows clipboard. You may have accidentally pasted that data into you spreadsheet from the clipboard.

 

[mdass79]

Hello, thanks for your reply. If I did copy something into the Windows clipboard, can you pls tell me how to delete the items from the clipboard?

How to delete the multiple links without destroying the existing data in the cell?

Thanks.

 

[The Villan]

To remove a hyperlink, you hover over the offending cell and click once with your right mouse.
A set of menu's should appear, and hopefully you will see at the bottom of the list "Remove Hyperlink"
Click once with the left mouse button and the link should be removed and the coloured background.

To check whats in the clipboard at any time, select the Edit menu and then select "Office Clipboard". Follow your nose from there.

What I did to clear your spreadsheet of all possible links and erroneous data that might have been in there, was the following.

1. Select "Window, Remove Split" so that I could now see all the rows in the spreadsheet.
2. I then clicked on the row border 15 (the grey block on the left hand side of the spreadsheet with 15 in it) which selected the complete row.
3. I then held the "Shift" key down on my keyboard and pressed the "End" key and the "Down Arrow" key, and then released the "Shift" key.

If done correctly all the rows from row 15 to 65536 should now be selected.

I then removed all these rows, by
1. Holding down the "Ctrl" key and then pressing the "Minus" key on your numeric keypad (if using a PC)
This will remove all the rows selected and replace with new rows (clean)

2. I then held the "Ctrl" key down and pressed the "Home" key and that takes my cell pointer back to cell A1.

I then did the same for the columns.
1. I clicked on the column border E (the grey block on the top of the spreadsheet with E in it) which selected the complete column.
3. I then held the "Shift" key down on my keyboard and pressed the "End" key and the "Right Arrow" key, and then released the "Shift" key.

If done correctly all the columns from column E to IV should now be selected.

I then removed all these columns, by
1. Holding down the "Ctrl" key and then pressing the "Minus" key on your numeric keypad (if using a PC)
This will remove all the columns selected and replace with new columns(clean)

2. I then held the "Ctrl" key down and pressed the "Home" key and that takes my cell pointer back to cell A1.

It is then important to save the spreadsheet and close it before continuing further with input etc. The reason for this, is that the active spreadsheet is still remembered and this makes the file larger than need be. In your case Excel had an active spreadsheet from A1 to HD30. You can always check what is active in a spreadsheet by holding down the "Ctrl" key and presseing the "End" key once. The active cell pointer should in this spreadsheet case now be in HD30.

When you re-open this spreadsheet and hold the "Ctrl" key down and press"End" you should now only go to D13

The Ctrl + End is a great way to see what area your spreadsheet covers at that point in time, and can amaze you at times.

Whenever I had to sort out peoples spreadsheets in the past, that was one of the first tthings I ever did. It told me a lot about the spreadsheet and its design.

Hope that helps and wasn't too confusing.

 

[mdass79]

Hello,

Thanks for your reply. your steps were not confusing at all, in fact they are helpful. Thank you again.

The problem that I have is to maintain the data in the cell after deleting the website link (The website is something that we view but we don't copy anything from there).

As I deal with thousands of records per day, it is kind of difficult to delete the hyperlinks one by one. Furthermore, I have to look for the hyperlinks first.

Please refer to the Employee Name and Company column of the attached file. If you hover the mouse in these two columns, you'll find that not all the cells are hyperlinked.

My enquiry would be

1) whether there's a way to delete all the hyperlinks without destroying the data in the cell
2) if deleting the hyperlinks one-by-one is the only choice i have, then is there a way to display all the links on the worksheet ? Similar to shows formulas (ie Tools | Options | Window Options|Formula)

Please help. Thank you so much for your time.

 

[The Villan]

OK I know from your soreadsheet that E19, E21 & E22 havehyperlinks in.
Assuming only column E has the hyperlinks, then do the following.

1. Select the whole of Column E
2. From the menu's click on Edit, Copy
3. From the menu's click on Edit, Paste Special
4. Select "Values" and click on OK
5. Press the Enter key on your keyboard once.

Check those cells mentioned above and the hyperlinks should have been removed, but the data remains. Obviously, you would not want to select a column that has formulas in

Hope that works OK.

 

[genesis_cat]

Okay, to delete every hyperlink and keep the text do the following:
Use the arrows to move your cursor to a cell that has a hyperlink in it.
Go to tooks, Macro, Record New Macro (I use Ctrl + X as my short cut key for this one).
Then right click, select hyperlink, delete hyperlink, and Stop the Macro from recording.
Now, select all cells in your worksheet by clicking on the empty gray cell to the far upper left (far corner between the "1" and the "A"). Then type your shortcut key (Ctrl + X) to run your macro and watch ALL of the hyperlinks disappear while the text still remains!!