as of today (19 Sep 2022), there are 72 locales in google sheets that can be accessed from
File > Spreadsheet settings
|
|
|
|
| argentina |
finland |
israel |
serbia |
| armenia |
france |
italy |
slovakia |
| australia |
georgia |
japan |
slovenia |
| azerbaijan |
germany |
kazakhstan |
south korea |
| belarus |
greece |
latvia |
spain |
| bolivia |
hong kong |
lithuania |
spain (catalan) |
| brazil |
hungary |
mexico |
sweden |
| bulgaria |
india |
mongolia |
switzerland |
| canada (english) |
india (bangla) |
myanmar (burma) |
taiwan |
| canada (french) |
india (gujarati) |
netherlands |
thailand |
| chile |
india (kannada) |
norway (bokmal) |
turkey |
| china |
india (malayalam) |
norway (nynorsk) |
ukraine |
| colombia |
india (marathi) |
paraguay |
united kingdom |
| croatia |
india (punjabi) |
philippines |
united kingdom (welsh) |
| czechia |
india (tamil) |
poland |
united states |
| denmark |
india (telugu) |
portugal |
uruguay |
| ecuador |
indonesia |
romania |
venezuala |
| egypt |
ireland |
russia |
vietnam |
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:
these are formula argument separators and each locale inclines toward one or the other. comma , is used in 28 locales:
|
|
|
|
| australia |
india (gujarati) |
ireland |
south korea |
| canada (english) |
india (kannada) |
israel |
switzerland |
| china |
india (malayalam) |
japan |
taiwan |
| egypt |
india (marathi) |
mexico |
thailand |
| hong kong |
india (punjabi) |
mongolia |
united kingdom |
| india |
india (tamil) |
myanmar (burma) |
united kingdom (welsh) |
| india (bangla) |
india (telugu) |
philippines |
united 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:
=INDEX(VLOOKUP(MID(FORMULATEXT(INDIRECT(ADDRESS(ROW(); COLUMN()))); 54; 1);
SUBSTITUTE(SUBSTITUTE(SPLIT({
",♣,♦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 syntax |
semicolon syntax |
| fx argument separator |
, |
; |
| array column stacking |
; |
; |
| array row stacking |
, |
\ |
| decimal separator |
. |
, |
or you can take a hint from the formula tooltip helper box:
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
|
|
|
| australia |
india (marathi) |
myanmar (burma) |
| canada (english) |
india (punjabi) |
philippines |
| china |
india (tamil) |
south korea |
| hong kong |
india (telugu) |
switzerland |
| india |
ireland |
taiwan |
| india (bangla) |
israel |
thailand |
| india (gujarati) |
japan |
united kingdom |
| india (kannada) |
mexico |
united kingdom (welsh) |
| india (malayalam) |
mongolia |
united 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:
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:
=ISDATE(A1)
or as arrayformula with secret hidden formula (yes, that's a thing) ISDATE_STRICT like:
=ARRAYFORMULA(ISDATE_STRICT(A1:A))
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:
|
|
|
|
| argentina |
colombia |
hong kong |
taiwan |
| bolivia |
ecuador |
mexico |
united states |
| chile |
egypt |
paraguay |
uruguay |
| china |
greece |
south korea |
venezuala |
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:
|
|
|
| hungary |
lithuania |
south korea |
| japan |
mongolia |
turkey |
| latvia |
portugal |
united 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:
|
|
|
|
| australia |
india (gujarati) |
india (tamil) |
spain |
| brazil |
india (kannada) |
india (telugu) |
spain (catalan) |
| france |
india (malayalam) |
indonesia |
united kingdom |
| india |
india (marathi) |
ireland |
united kingdom (welsh) |
| india (bangla) |
india (punjabi) |
israel |
vietnam |
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:
|
datetime |
targeted |
compromise |
| armenia |
24.01.09, 16:00 |
dd.mm.yy h:mm |
without comma |
| georgia |
24.01.09, 16:00 |
dd.mm.yy hh:mm |
without comma |
| mongolia |
2009-01-24, 16:00 |
e-mm-dd hh:mm |
without comma |
| thailand |
24/1/2009, 16:00:00 |
d/m/e h:mm:ss |
without comma |
| egypt |
4:00:00 م 2009/01/24 |
e/mm/dd h:mm:ss am/pm |
time date swap |
| myanmar (burma) |
16:00 24-01-09 |
dd-mm-yy hh:mm |
time 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:
=GOOGLETRANSLATE("hello")
or as:
=GOOGLETRANSLATE("hello"; "auto"; "auto")
is supported only by 16 locales:
|
|
|
|
| brazil |
germany |
mexico |
spain |
| canada (english) |
hong kong |
philippines |
taiwan |
| china |
italy |
portugal |
united kingdom |
| france |
japan |
south korea |
united states |
jumping on the newest LAMBDA train with:
={"google translate support";""; INDEX(IFERROR(IF(REGEXMATCH(BL3:BL; "en");
BYROW(BL3:BL; LAMBDA(r; GOOGLETRANSLATE("谢谢"; "zh"; r)));
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:
|
|
| bulgaria |
india (malayalam) |
| india |
india (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:
and not even 1465 km from vietnam there are philipphines:
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
list of all 27 locales that by default produce double currency systems:
|
|
|
| armenia |
india (bangla) |
philippines |
| azerbaijan |
india (gujarati) |
romania |
| bolivia |
india (kannada) |
russia |
| bulgaria |
india (malayalam) |
serbia |
| canada (english) |
india (marathi) |
switzerland |
| denmark |
india (punjabi) |
taiwan |
| egypt |
india (tamil) |
turkey |
| hong kong |
india (telugu) |
venezuala |
| india |
paraguay |
vietnam |
denmark even spits out just a dot for some unknown reason:
and 13 locales with secondary currencies do not even support symbols under default fonts
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 |
| switzerland |
Fr\. 0.00 |
| russia |
р\.0.00 |
| belarus |
0.00 p. |
| denmark |
0.00 kr. |
| paraguay |
0.00G\s. |
| ukraine |
0.00 грн. |
the world map of 72 supported locales:
