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.

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
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. Commented Mar 29, 2020 at 18:29
  • There was a surprising solution. Please see below Commented Mar 30, 2020 at 13:42
  • @JoshuaFox Thanks for the heads up. Commented Mar 30, 2020 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

Improve this answer
2
  • Interesting. I'm wondering how that entity was entered. Did you copied or imported the values? Commented Mar 30, 2020 at 14:11
  • Yes, this was copied from LibreOffice from a downloaded Hebrew spreadsheet Commented Mar 30, 2020 at 15:01

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.