Excel data wont re-format or calculate

Question

I have a spreadsheet that was created from a webapp. I save it as an excel wkbk. I close file and go to file and open from my pc. When I try to reformat a column of numbers, nothing happens. (it says the format is 'general' and alignment is 'general & top') it appears as all numbers, left aligned.

I have tried to 'unlock' the cells and try again and nothing happens. If i retype the numbers in cell, it seems to 'break' the problem and create a true number. If I go to a cell away from the data and enter a '1', then highlight/copy, and then highlight the cells with the data/numbers and paste special/multiply - i get no change. I tried to write a formula to a new column with adding and also another with multiplying and get '#value!' as the result.

Sorry this is long, I want to be sure you know the ground I have covered in exploring this. We have saved spreadsheets created from other web-based applications with no difficulty in manipulating the data. I appreciate any help you have to offer.

Answer 1

there might be a leading space before the digits. I have that problem when I paste account data from my credit union. basically, it sounds like it thinks the numbers are formatted as text. maybe try pasting into Word or Notepad to see hidden or extra characters.

I'm assuming you've tried changing the format from 'general' to 'number', but you can also try paste special into a new column and just paste values.

Also, it only takes one leading space to break the column from numeric. Try replacing spaces in that column with nothing.

Answer 2

I have seen behavior similar to this when an Excel binary file has been generated by a component. In that case, all of the data had been written to the binary file as if it were a string. This seemed to prevent Excel from using the cell contents in a formula.

If you are the owner of the web application, you may want to see if the component you are using allows the data to be output correctly as number (or RK values) in the Excel binary file.