4

Lets say I have following CSV file:

1.0;0.125;0.1253

Opening it in Numbers gives me:

enter image description here

Why are 1.0 and 0.1253 correctly presented as "1.0" and "0.1253", but "0.125" is presented as "125"?

What do I have to do to get Numbers to display "0.125" correctly?

Edit: My region and language settings are set to Europe/Germany.

Improve this question
2
  • Your test CSV works correctly in Numbers (for me). What are your macOS language settings?
    – Gilby
    Commented Mar 30, 2023 at 2:27
  • @Gilby My region and language settings are set to Europe/Germany. I have added this as a note to the question.
    – kostrykin
    Commented Mar 30, 2023 at 11:52

1 Answer 1

Reset to default
4

It looks like Numbers is recognizing the dot as a thousands separator rather than a decimal point when a decimal has a sole leading zero and exactly three digits after the decimal point.

As a workaround, you can instruct Numbers to treat all dots as decimal points. To do that, use the Numbers' import settings menu which you can reach by clicking on the "Table data was imported and can be adjusted. >" popup warning that would appear right after a text or CSV files is opened by Numbers.

Then, in Import Settings, go to Advanced Settings and enter . for the Decimal Separator option and click on Update Table.

You can reach Import Settings also through the "Adjust Import Settings..." option at the bottom of the Format->Table sidebar.

Another option is to remove all the sole leading zeros from all decimal points in the CSV file, i.e. use .125 instead of 0.125 or .3 instead of 0.3.

UPDATE

This happens because your macOS Region setting is Germany and, for Germany, the default Number format under System Settings -> General -> Language & Region is 1.234.567,89. This Number format setting unfortunately causes Numbers to recognize the dot in a decimal with a sole leading zero and exactly three digits after the dot as a thousands separator.

A workaround other than those I suggested earlier would be to change the Number format setting. This can be accomplished by changing the Region from Germany to USA or UK or by only changing the Number format setting to 1,234,567.89 or 1 234 567,89. In the latter Number format setting, however, as would be with the current setting, all decimals which have the dot as the decimal point would be imported into Numbers as text by default rather than numbers.

Improve this answer
5
  • Thanks for pointing that out. However, I'm opening many dozens of CSV file a day, and it is not feasible to do that via explicit import every time. I need to do it via double click. Is it possible to tell Numbers to import everything as pure text? I don't need any kind of fancy processing. All I want is to see the raw data in the CSV.
    – kostrykin
    Commented Mar 29, 2023 at 16:59
  • Sorry, but I don't know at this time how you can make this work without dealing with the import settings in Numbers every time. You may either try another spreadsheet program or if you need to be using Numbers, you might look into preprocessing your CSV files in order to remove the leading sole zeroes in decimals using a macOS command line tool such as awkor a programming language like Python.
    – Alper
    Commented Mar 29, 2023 at 18:27
  • On another thought, if viewing the CSV files as pure text is enough for you, why don't you open them using the macOS's text editor, TextEdit, instead of Numbers? In case necessary, you can change the default program for opening text files as described in the "Choose an app to open a file on Mac" Apple Support webpage.
    – Alper
    Commented Mar 29, 2023 at 20:06
  • "This can be accomplished by changing the Region from Germany to USA or UK or by only changing the Number format setting to 1,234,567.89 or 1 234 567,89." — am I right that these changes would apply system-wide and not only for numbers?
    – kostrykin
    Commented Mar 31, 2023 at 16:57
  • @theV0ID Yes, for all macOS.
    – Alper
    Commented Mar 31, 2023 at 22:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .