Excel number format - doesn't read as a number

%3CLINGO-SUB%20id%3D%22lingo-sub-1283948%22%20slang%3D%22en-US%22%3EExcel%20number%20format%20-%20doesn't%20read%20as%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283948%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20problem%20with%20an%20excel%20sheet%20where%20some%20of%20the%20numbers%20look%20like%20a%20number%20with%20no%20decimals%20and%20with%201000%20separator.%20Excel%20doesn't%20read%20this%20as%20number%20and%20it%20is%20impossible%20to%20change%20format.%20I%20have%20changed%20format%20to%20number%2C%20and%20it%20says%20that%20it's%20a%20number%2C%20but%20even%20if%20I%20add%20decimals%20or%20remove%201000%20separator%2C%20nothing%20happens%20to%20the%20number%20in%20the%20cell.%20Down%20in%20the%20corner%20of%20the%20document%2C%20it%20only%20counts%20number%20of%20cells%2C%20but%20not%20the%20value%20in%20the%20cells.%20This%20applies%20to%20a%20rather%20large%20amount%20of%20data%2C%20so%20it%20is%20out%20of%20the%20question%20to%20type%20the%20cells%20in%20a%20neighbouring%20cell....%20I%20have%20also%20tried%20copy%20number%20(123)%20and%20copy%20format%20from%20cells%20that%20has%20the%20correct%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1283948%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1284040%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20number%20format%20-%20doesn't%20read%20as%20a%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1284040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F610625%22%20target%3D%22_blank%22%3E%40Hilde250%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20initially%20cells%20were%20in%20text%20format%20and%20cell%20values%20were%20shown%20as%20text.%20Just%20changing%20the%20cell%20format%20to%20General%20or%20Number%20is%20not%20enough%2C%20in%20addition%20values%20are%20to%20be%20re-entered.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20data%20is%20in%20the%20column%2C%20after%20changing%20the%20format%20you%20may%20Data-%26gt%3BText%20to%20Columns-%26gt%3BFinish.%3C%2FP%3E%0A%3CP%3EAlternatively%20select%20Paste%20Special-%26gt%3BOperations-%26gt%3BAdd%20zero%20to%20all%20cells%20in%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a problem with an excel sheet where some of the numbers look like a number with no decimals and with 1000 separator. Excel doesn't read this as number and it is impossible to change format. I have changed format to number, and it says that it's a number, but even if I add decimals or remove 1000 separator, nothing happens to the number in the cell. Down in the corner of the document, it only counts number of cells, but not the value in the cells. This applies to a rather large amount of data, so it is out of the question to type the cells in a neighbouring cell.... I have also tried copy number (123) and copy format from cells that has the correct format.

 

Please help!

1 Reply
Highlighted

@Hilde250 

It looks like initially cells were in text format and cell values were shown as text. Just changing the cell format to General or Number is not enough, in addition values are to be re-entered.

 

If data is in the column, after changing the format you may Data->Text to Columns->Finish.

Alternatively select Paste Special->Operations->Add zero to all cells in question.