How to force English number format for all number values in a column?

Question

This is what I see by default in my table:

Pasted   Value	Displayed Value
2	2,00
2	2,00
0	0,00
0	0,00
0.5	0.5
2	2,00
0	0,00

All except 0.5 are aligned as numbers to the right. 0.5 is treated as string.

I tried various number formats, but none are willing to accept that 0.5 is a number, not willing to display . as the decimal separator. Instead, I see stuff like this:

Format: #.##0

The document is set to Czech language and changing the locale will break other things. I need this column in particular to act as if the language is English.

Answer 1

The document is set to Czech language and changing the locale will break other things. I need this column in particular to act as if the language is English.

1. This is about the locale setting for which Czechia, United States and United Kingdom are valid, but Czech and English are not.

2. The display language (language) is tied to your Google account and should not be a factor.

3. Comma not period is the decimal separator for Czechia so the spreadsheet won't recognize #.##0 as a valid number format in that locale.

   locale:Czechia            locale:United States 
   0,5 =  1/2  // number      0,5 <> 1/2  // text 
   0.5 <> 1/2  // text        0.5 =  1/2  // number

4. Some options to resolve:

   1. Right-align the text
   2. Temporarily change the locale
   3. Create locale-independent results

A.  Right-align the text

1. Leave the locale:Czechia

2. Leave the number format set to 'Automatic'

3. Paste the values which can be either:

   • a mix of text and numbers, eg. 2, "0.5", 0
   • all text and simulate the decimal marker offset as needed. eg. "2   ", "0.5", "0   "

4. Right-align the values

   Text and Values                 Text Only (incl. padding)
        

B.  Temporarily change the locale

1. Change the locale to one that uses the period as a decimal separator, United States for example, all the formulas should update correctly to match the locale.^*
2. Paste your North American formatted numbers into the spreadsheet.
3. Return the locale to Czechia

^* If changing the locale breaks the formulas, it indicates other issues with the spreadsheet (eg. text strings used as values in calculations). If that's the case, it would be best to address any issues with the formulas so that they are locale-independent, and in that way the spreadsheet will always return consistent results regardless of the locale.

C.  Create locale-independent results

If you simply want certain columns or values displayed according to a specific format, you can separate the display format of that column (or several columns) from the underlying value calculations.

1. Insert one or more columns anywhere in the spreadsheet to display values according to your preferred format (independent of the current locale).
2. Write an array formula to retrieve the values in question and convert them for display. For example, if your values are in column A, then depending on the locale, the formula would be either:

   =ARRAYFORMULA(LET(
      range,A2:A, cze,REGEXMATCH(E1, "Cze"), 
      fraction,IFNA(REGEXEXTRACT(TO_TEXT(range*1), "[\.,](\d*)")),
      IF(ISNUMBER(range), 
        LET(num,REGEXREPLACE(""&INT(range), " ", ), 
          IF(LEN(num)>3,
            REGEXREPLACE(num, "(\d{3})$",IF(cze," ", ",")&"$1"), num))&
            IF(cze,", ", ".")& 
            LEFT(fraction& REPT(0,15), MAX(LEN(fraction))),)))
   

   or

   =ARRAYFORMULA(LET(
      range;A2:A; cze;REGEXMATCH(E1; "Cze"); 
      fraction;IFNA(REGEXEXTRACT(TO_TEXT(range*1); "[\.,](\d*)"));
      IF(ISNUMBER(range); 
        LET(num;REGEXREPLACE(""&INT(range); " "; ); 
          IF(LEN(num)>3;
            REGEXREPLACE(num; "(\d{3})$";IF(cze;" "; ",")&"$1"); num))&
            IF(cze; ","; ".")& 
            LEFT(fraction& REPT(0;15); MAX(LEN(fraction)));)))
   

3. Using a dropdown or similar, select the preferred format for the results. For example:
   1. Here, the spreadsheet's locale is set to the United States:
      
      
      
   2. Here, the spreadsheet's locale is set to Czechia: