Excel number formatting thousands is not working

Question

With Excel, I am trying to format my numbers to display as thousands. 1000 should be 1 k. I usually use the format numbers from Excel. Depending on the language, I add a "space" or a "," after the last digit of my formatting in order to get a x1000 division.

I used to have: Format numbers in thousand: 0, "K" (US) or 0 space "K" (French, for instance) Format numbers in millions: 0,,"M" (US) or 0 double-space "M" (French, for instance)

However, it is not working anymore and now it just displays my numbers with a comma/two commas at the end instead of having thousands or millions.

Do you know what it could be related to and how to parameter the cells format?

In my Advanced settings, I am using the "," as Decimal separator, and the " " as Thousands separator. I unchecked the regional parameters.

Here are the details of my settings: Advanced settings

Here is what is shown on several examples: Details of cases not working

Note: I would like to avoid having a formula as I only want to act on the cells format. For now, my workaround (not satisfying) is creating a "mirror" page using =ROUND(Cell/1000000;0) to display Millions, for instance

Thank you for your help

Answer 1

Number Format (Excel Advanced settings: Use System Separators

• If Region is set to French (France):

   <1000]# ##0 \€;[<1000000]# ##0  k\€;# ##0   \m\€
  

• If Region is set to English (United States)

   [<1000]#,##0 €;[<1000000]#,##0, k€;#,##0,, \m€
  

And if you change the region, and close and re-open the workbook, the settings will switch.

Opened with Region = US english settings

Closed then Re-opened with Region = French France

You may find it simpler to set the number format using the US region settings to avoid confusion between <space> as a space vs <space> as a thousands separator