[SOLVED] locale differences in google sheets (documentation missing pages)


This Content is from Stack Overflow. Question asked by player0

a fairly simple question cultivated from documentation failure of the google sheets project manager!

  • why there are syntaxes with commas and semicolons
  • why and when they are (not) compatible
  • what are the array literal and array row errors
  • why numbers, date, times and datetimes are not recognized
  • are AM/PM formats supported in all locales
  • what is the best way of converting formula syntaxes
  • which locale is not supported by GOOGLETRANSLATE formula
  • why there are multiple currency formats
  • when is the auto-translate not supported
  • what are the language codes for import functions
  • how to avoid tofu symbols when switching between locales
  • are there any currency bugs worthy of disappointment
  • what else is affected by locales
  • can we reproduce datetimes and currencies within TEXT function
  • how to fix and avoid formula parse error

no wonder humanity fails to populate other planets with such faulty spreadsheets and poor way to convey the full extent of functionalities


as of today (19 Sep 2022), there are 72 locales in google sheets that can be accessed from
File > Spreadsheet settings


azerbaijangermanykazakhstansouth korea
boliviahong konglithuaniaspain (catalan)
canada (english)india (bangla)myanmar (burma)taiwan
canada (french)india (gujarati)netherlandsthailand
chileindia (kannada)norway (bokmal)turkey
chinaindia (malayalam)norway (nynorsk)ukraine
colombiaindia (marathi)paraguayunited kingdom
croatiaindia (punjabi)philippinesunited kingdom (welsh)
czechiaindia (tamil)polandunited states
denmarkindia (telugu)portugaluruguay

each locale has its own set of unique formatting rules and quirks based on the country which they mirror. the spreadsheet world is divided into 2 major syntax groups:

  • comma ,
  • semicolon ;

these are formula argument separators and each locale inclines toward one or the other. comma , is used in 28 locales:

australiaindia (gujarati)irelandsouth korea
canada (english)india (kannada)israelswitzerland
chinaindia (malayalam)japantaiwan
egyptindia (marathi)mexicothailand
hong kongindia (punjabi)mongoliaunited kingdom
indiaindia (tamil)myanmar (burma)united kingdom (welsh)
india (bangla)india (telugu)philippinesunited states

the rest of them use a semicolon ;


if you are not sure about your spreadsheet you can run this unique universal formula in any cell of your spreadsheet to check:

 ",♣,♦fx argument separator♠;♦array column stacking♠,♦array row stacking♠.♦decimal separator";
 ";♣;♦fx argument separator♠;♦array column stacking♠\♦array row stacking♠,♦decimal separator"}; 
 "♣"); "♠"; CHAR(10)); "♦"; "     "); 2; IFERROR(NOW()/0)))


fun fact: this formula reports itself without violating circular reference so there is no need to enable iterative calculation!

at this point, 50% of you may have noticed that after running the above formula all semicolons ; were auto-corrected to commas ,

yes, google sheets is able to auto-correct semicolons into commas if your locale is one of those 28 that uses commas (and after the latest update it applies even to conditional formatting, data validation and named functions)

keep in mind that a comma is never auto-corrected into a semicolon if you are on the dark side of the spreadsheets so watch your commas!

also, it’s worth mentioning that backslash \ is not auto-corrected to a comma , in array {} construct! if you mess up stacking cells/ranges into rows you will encounter ARRAY_ROW ERROR. this error is the same as ARRAY_LITERAL ERROR – but for stacking stuff next to each other. it is a common mistake to take a comma syntax variant and just replace all commas with semicolons and it surely errors out if there is an array {} construct containing a comma.

the best practice to convert the syntax of the formula (especially if it’s some advanced complex fx) is to:

  • change locale to fit the formula syntax
  • enter it in any cell
  • and change back to the initial locale

this way all the separators get auto-converted with zero chance of failure (usually flagged as array_literal, aray_row or formula parse error) so to summarize it in 99% applies the following:

comma syntaxsemicolon syntax
fx argument separator,;
array column stacking;;
array row stacking,\
decimal separator.,

or you can take a hint from the formula tooltip helper box:

enter image description here

now here is where the nightmare begins. one locale does not automatically recognize the formatting of another locale after the switch happens! it almost works with numeric values (Numbers) but it completely fails when it comes to Dates, Times, Datetimes and Currencies.

currently, there are 6 acknowledged formats for numbers:


your eagle eye may immediately spot the A3:A4 issue. as thousands separator both of them use empty space, but the empty space in A4 is shorter! yes, that’s france locale. A5 is specific only to arabic language symbol group so that’s egypt locale and A6 is of course spain with "smart apostrophe". by omitting that awkward short-space thousands separator we can divide it to:


not so bad right? wrong! welcome to india where you can find thousands separator combined with hundreds separator for Lakhs and Crores (also meet Arab, Kharab, Nil, Padma, and Shankh)

1 Lakh    = 1,00,000
1 Crore   = 1,00,00,000
10 Shankh = 10,00,00,00,00,00,00,00,000

and while you are at it you may go berserk with short custom number formatting

back to the topic… if you don’t care about number formats and you like it pure and raw we can group locales into 3 groups of decimal separators where dot . is used by

australiaindia (marathi)myanmar (burma)
canada (english)india (punjabi)philippines
chinaindia (tamil)south korea
hong kongindia (telugu)switzerland
india (bangla)israelthailand
india (gujarati)japanunited kingdom
india (kannada)mexicounited kingdom (welsh)
india (malayalam)mongoliaunited states


the rest uses comma , and egypt has its own arabic separator ٫

sadly, Dates are a whole new chapter of mess. there are 19 unique formats which is totally fine & understandable but they are not backward compatible! the most common format being dd/mm/yyyy:

enter image description here

by not having the right locale for chosen date format may cause you, that your dates won’t be recognized as valid dates in your formula and you will need to modify them like in this example or like in this one. also, let’s don’t forget the epoch/unix dates and that SQL QUERY recognizes only yyyy-mm-dd format.

you can check the validity of the date with ISDATE like:


or as arrayformula with secret hidden formula (yes, that’s a thing) ISDATE_STRICT like:


there is no time like time… the most common format being hh:mm:ss there are surprisingly also:


norway (bokmal), norway (nynorsk) and finland having their own klo/kl. thing while armenia, azerbaijan, georgia, mongolia and myanmar (burma) do not recognize seconds as something of importance! the majority goes with full time format, there are 16 who prefer AM/PM time format:

argentinacolombiahong kongtaiwan
boliviaecuadormexicounited states
chinagreecesouth koreavenezuala

when targetting times with formula like TEXT watch out for dot . separator for time instead of the colon : when on denmark, finland, italy, norway (bokmal), norway (nynorsk), sweden locale and those above mentioned klo/kl. time prefixes.

while AM/PM format is supported by all 72 locales the short AM/PM (A/P) format is not supported by:

hungarylithuaniasouth korea
latviaportugalunited kingdom (welsh)

moving to Datetimes, it looks like there are 28 unique combinations:


the most common being the format dd/mm/yyyy hh:mm:ss within 20 locales:

australiaindia (gujarati)india (tamil)spain
brazilindia (kannada)india (telugu)spain (catalan)
franceindia (malayalam)indonesiaunited kingdom
indiaindia (marathi)irelandunited kingdom (welsh)
india (bangla)india (punjabi)israelvietnam

and the rest varies. some of them use AM/PM, some don’t use seconds, others use time prefixes klo/kl. and hong kong even uses suffixes for year , month and day . again, variations are totally fine but the issue is targeting them with TEXT formula. this reveals, that there are not 28 but 34! unique combinations:


where it is important to mention that 4 locales are not possible to target! and two locales can be mimicked with a compromise where the order needs to be swapped:

armenia24.01.09, 16:00dd.mm.yy h:mmwithout comma
georgia24.01.09, 16:00dd.mm.yy hh:mmwithout comma
mongolia2009-01-24, 16:00e-mm-dd hh:mmwithout comma
thailand24/1/2009, 16:00:00d/m/e h:mm:sswithout comma
egypt4:00:00 م 2009/01/24e/mm/dd h:mm:ss am/pmtime date swap
myanmar (burma)16:00 24-01-09dd-mm-yy hh:mmtime date swap

and spain being weirdo with one d but two mm for month – d/mm/e h:mm:ss.
slovenia (d. m. e h:mm:ss) and south korea (e. m. d am/pm h:mm:ss) having spaces after dots . …and just to clarify, by "targetting datetime" is meant to recreate the exact format and by multiplying TEXT fx with 1 not getting any errors so the recreation could stand as valid datetime value whenever needed.

the rest of the world should take a moment and appreciate united states not having time in the imperial system – whatever it would look like

how to sum time is always nice know-how whenever the time is right

the next is the difference of TEXT strings:

ddd   - short days of week names
dddd  - full days of week names
mmm   - short month names
mmmm  - full month names
mmmmm - first character of month names

every locale follows local customs so let’s call it that everybody is unique *cought*


the issue here is with myanmar (burma) where characters are not supported by default, nor the majority of fonts where the result are tofu symbols:


so it is necessary to find and add a font for this fix. Padauk being the one for example:


then there is google translate. 71 out of 72 locales do support googletranslate. the black sheep being the norway (nynorsk) locale. on the other hand, auto-translate as:


or as:

=GOOGLETRANSLATE("hello"; "auto"; "auto")

is supported only by 16 locales:

canada (english)hong kongphilippinestaiwan
chinaitalyportugalunited kingdom
francejapansouth koreaunited states

jumping on the newest LAMBDA train with:

={"google translate support";""; INDEX(IFERROR(IF(REGEXMATCH(BL3:BL; "en"); 
 BYROW(BL3:BL; LAMBDA(r; GOOGLETRANSLATE("thanks"; "en"; r))))))}


yes, the right language code for united kingdom is en-GB not en-UK which works too btw. all locales have its own unique language code jointly for IMPORTXML, IMPORTDATA and IMPORTHTML formulas as they should.

lastly, let’s address Currencies. by exploring the currency button on the toolbar we can learn how much devs don’t care about fixing the bugs and official documentation!

one would assume wrongly that Euro as a currency of the European Union would be the same in all states. there are 3 variants:


italy, netherlands and slovenia didn’t get the memo for globally unified currency system and ireland decided not to bother with space too.

belarus and ukraine adds trailing space after the currency value!


bulgaria, russia, serbia and ukraine currencies are written in cyrillic while belarus is not written in cyrillic so we get the russia – belarus visual mess:

belarus - 1,00 p. 
russia  - р.1,00

(the order value>symbol vs. symbol>value is irrelevant)

then we have a visual bug on:

bulgariaindia (malayalam)
indiaindia (marathi)
india (bangla)india (punjabi)
india (gujarati)india (tamil)
india (kannada)india (telugu)

where on the toolbar button there is a dot . after currency but pressing that button won’t produce any dot!


what if we have a currency symbol on the toolbar button that produces a completely different currency symbol after pressing it? greetings to vietnam:

enter image description here

and not even 1465 km from vietnam there are philipphines:

enter image description here

still not enough? how about locales which detects different currency under different conditions eg. pressing the currency button will produce something else than taking the route of
Format > Number > Custom currency > Default detected suggestion

enter image description here

enter image description here

list of all 27 locales that by default produce double currency systems:

armeniaindia (bangla)philippines
azerbaijanindia (gujarati)romania
boliviaindia (kannada)russia
bulgariaindia (malayalam)serbia
canada (english)india (marathi)switzerland
denmarkindia (punjabi)taiwan
egyptindia (tamil)turkey
hong kongindia (telugu)venezuala

denmark even spits out just a dot for some unknown reason:

enter image description here

and 13 locales with secondary currencies do not even support symbols under default fonts

enter image description here

even targeting currencies of locales within TEXT formula is not as straightforward as one would imagine. no matter of locale you are on, currency uses a dot . as a decimal separator, so the syntax for the value is always 0.00.

then every individual currency needs to be targeted differently because there is a lack of a universal symbol/character that would automatically get the currency of chosen locale. "but, but we have DOLLAR for that" – yeah, another of many redundant functions that is just mirroring the toolbar button (and sneaks in trailing space for belarus and ukraine locale). and inside a SQL argument of QUERY we have no use of DOLLAR tho.

a few examples for TEXT formula where certain stuff and dots . before values needs to be escaped with backslash \ like:

serbiaДин\. 0.00
switzerlandFr\. 0.00
belarus0.00 p.
denmark0.00 kr.
ukraine0.00 грн.

the world map of 72 supported locales:

enter image description here

enter image description here

pdf demo

spreadsheet demo

This Question was asked in StackOverflow by player0 and Answered by player0 It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?