I’ve set the format on a column to Number but when I enter a number into a cell the format is Text. Why is the cell not honoring the column’s Number format?
- Please upload an ODF type sample file here.
- Please give us informations about the LO version.
- Please give us informations about the applied formatting method (Direct or by Styles - the sample file will show it for us)
Please give an example of the number. The information about the LO version - if it would be the full data from Help → About - would include the locale used in the program; and then it could turn out, that the “number” like 123.4567 would use incorrect decimal separator…
Untitled 1.xlsx (6.1 KB)
To see the problem:
- Select column A
- Format Cells to Number/General
- Type 5 into cell A7
- Check cell format of A7
- It’s Text/@ instead of the expected Number/General
Version: 7.6.2.1 (X86_64) / LibreOffice Community
Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
There is a mix of Number and Text formatting already in the column. You could
- select from A5 to end of column and then press Ctrl+M to remove direct formatting. Or
- Select column A and change format to Scientific, OK. Then change format to Number > General, OK
You also need to change A2 & A3 to number format and delete the apostrophe at the beginning of the text to turn them into numbers. You might find this page useful, How to convert number text to numeric data
tdf#153527 ?
1 Like
In your example, cells A2:A3, A6:A7, A9:A26 are in text format, the rest of the cells in column A (A1, A4:A5, …) are in Standard format.
When you select Column A and open the Format Cells dialog, the Format list shows Standard.
If you just click OK, nothing will happen; you need to change something in the dialog.
For example, specify the value Text in the Category list and click “OK”, then open the dialog, change Text to Number and click OK. After this, all cells in column A will have a standard format.
I think that in the case where cells of selected range have different format categories, the Category list of Format Cells dialog should not have a selected item (as it does in Excel).