As it can be seen below, I have cells with dates and numbers. The latter are in format "Number". However, the numbers have an apostrophe (') at the beginning of each cell. This can be seen in the red circle.
This symbol makes the cell not to be an actual number. When eliminated, numbers are recognized, as in the first, 9.4 example, with the alignment to the right. Then I thought, well, I will eliminate them using the "Find & Replace" option. However, LibreOffice can't find the symbol! I have hundreds of cells, so a manual solution is not plausible.
UPDATE: I have done several tests to pin down the problem:
I have two keyboard layouts. Tried with both. No change.
Save file in different format, nothing.
In the row CR I have a
=RIGHT(CQ#,3)
formula to get rid of the apostrophe but it does not work, as it can be seen in the image.If I create a new cell manually with, for example,
'20
, it still cannot find it.Interestingly,
concatenate
formula give weird results (values taken from cells, not input as below):=concatenate(20,'10)=2010
=concatenate("asd",'10)=asd10
Even more interestingly, if the symbol
'
is together with a random collection of symbols, it can be found: for example, in a cell containing'*^£@'#!('
This could be the crucial evidence. If I reformat the cell as a "Text", then the apostrophe disappears (but the value continues at the left of the cell, so not a number!). However, if I reformat it back to number, the apostrophe comes back. This is very weird stuff......