Locale can flip what the , and . operators mean

[borgar]

This is the content of a workbook file with both versions of the formula, saved under the German locale:

<sheetData>
  <row>
    <c r="A1" t="str">
      <f>TEXT(0, "#.##0,00")</f>
      <v>0,00</v>
    </c>
  </row>
  <row>
    <c r="A2" t="str">
      <f>TEXT(0, "#,##0.00")</f>
      <v>,000</v>
    </c>
  </row>
</sheetData>

What is needed to support this is an option to parse/treat the separators according to the locale:

numfmt("#.##0,00", { locale: "de", localeParse: true })(0) // 0,00
numfmt("#,##0.00", { locale: "de", localeParse: true })(0) // ,000
numfmt("#.##0,00", { locale: "de", localeParse: false })(0) // ,000
numfmt("#,##0.00", { locale: "de", localeParse: false })(0) // ,000

[borgar]

I've made an attempt at solving this within the parser (as per example above) and I didn't like it. It added execution time for all patterns as well as too much complexity. A better approach may be something like this:

const pattern = numfmt.delocalize("#.##0,00", { locale: "de" }); // "#,##0.00"
const formatter = numfmt(pattern, { locale: "de" });
const output = formatter(0); // 0,00

As visible in #27 the problem also extends beyond the comma and decimal point: Color names are localized as well which either means providing translation tables for all 8 colors (black blue cyan green magenta red white yellow) for all languages, or an option or callback to provide translations/color names, or just being liberal about this and allowing anything inside [] not identified as "something else" (condition, duration, locale...) as color.

[borgar]

This goes deeper. The operators also change across locales! =TEXT(A1; "JJJJ-MM-TT") 🙀

• y → J
• d → T

See: https://support.microsoft.com/de-DE/Excel/functions/text-function

[borgar]

LibreOffice have a very good table of this:

Locale	Year	Month	Day	Hour	Day Of Week	Era
English - en and all not listed locales	Y	M	D	H	A	G
German - de	J		T
Netherlands - nl	J			U
French - fr	A		J		O
Italian - it	A		G		O	X
Portuguese - pt	A				O
Spanish - es	A				O
Danish - da				T
Norwegian - no, nb, nn				T
Swedish - sv				T
Finnish - fi	V	K	P	T