Windows Forum

Ask something
Discussion Locked

Invisible data in Excel

0 helpful
9 replies

by bleone

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have some recurring tasks where I manually copy some data from an Access table and paste it into an Excel spreadsheet. This has worked fine for a least a year. Lately after pasting into Excel the data is invisible to formulas. Formulas read the cell as blank. But the value property of the cell is correct. I am using Win98 and Office 2000. Any ideas?

9 replies

by nikki96

Have you tried formatting the cells as numeric, not text? Copy/paste will sometimes mess that up and make Excel think it is text, and thus formulas do not apply. If you need help on how to format the cells as numbers, let me know.

by borginva

Is this a current active worksheet or a brand spanking new sheet? This would be of some help to me to know.
Any extra spaces in these fields when pasting?
Have you tried, for testing, pasting the info into Notepad first (because it has no formatting) and then copying from Notepad into Excel?
Let me know.

by bleone

This is an existing excel template. No spaces. I have not pasted into notepad yet but I tried pasting back and forth into several excel columns with no change.

by quintar51

The reason the formulas see the cells as blank is because it doesn't recognize the numbers. This happens usualy when you paste from a database, such as access.

let's say you have your numbers in column A.

What you'll need to do is in a new column, multiply all the numbers in column A by 1. This will convert the numbers into a format excel can understand.

Then you simply copy the results into another blank column, paste special - values only. This will now give you a second column with only the numbers, not the formulas as the first column did.

Copy this new column over column A with the 'unrecognizable' numbers. Your formulas should work now.

by bleone

Yes this works! I wish I knew why. The multiplying by 1 is what makes the difference.

by randalbin

Rather than copy and paste perhaps you should use the export function from MS Access. With the table or report open, choose File and Export. You have the option to select the file type to export into and there are several versions of Excel listed. With the data in an Excel spreadsheet you should be able to import it (or even copy it) into your target spreadsheet.

by bleone

I have not tried this but it is one of my long term goals. I will have to constantly create a unique and complicated sql statement. Right now I just manually select the desired data.

This discussion is closed to new replies.