0

In this sheet, the numbers are actually text.

I tried Format->Number then Number, Percent and Scientific, and they still appear as text.

As experiments: I cannot add 1 to these, and I cannot pass them to the value() function. They do not have an apostrophe at the beginning, which would make them text.

How can I format these numbers as numbers?

enter image description here


Edit:

I deleted the link to the sheet, because the excess character (which I identified as the cause) mysteriously disappeared from the sheet as well as historical versions which in principle are immutable! See answer below.

2

Since the numbers in column A are aligned to the left, assuming that the default formatting is applied to those cells, your spreadsheet is using comma as decimal separator, not a dot.

Alternatives

  1. Change the spreadsheet regional setting to one that use dot as decimal separator like United States or Mexico among others.

  2. Use comma as decimal separator instead of dot.

Related

3
  • Thank you, but it is not the comma. I edited it to remove the comma and it made no difference. The sheet now has one number with comma as separator, one with dot as if it were separator, one with dot as decimal point, and one with neither, and the problem is the same in all cases.
    – Joshua Fox
    Mar 29 '20 at 18:29
  • There was a surprising solution. Please see below
    – Joshua Fox
    Mar 30 '20 at 13:42
  • @JoshuaFox Thanks for the heads up.
    – Rubén
    Mar 30 '20 at 14:12
1

There was a hidden HTML Entity &#8324 "Right to Left"!

To see it, use F12 Developer Tools (Right Click->Inspect won't work as Google Sheets takes over right-click).

In fact, this entity is so unusual that Google Sheets may make it disappear in circumstances that I don't quite understand. The sheet somehow fixed itself. The problem resolved itself even in the historical revisions that I couldn't have fixed by accident.

enter image description here

2
  • Interesting. I'm wondering how that entity was entered. Did you copied or imported the values?
    – Rubén
    Mar 30 '20 at 14:11
  • Yes, this was copied from LibreOffice from a downloaded Hebrew spreadsheet
    – Joshua Fox
    Mar 30 '20 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.