Every Locale Listed in Google Sheets and a Tool to Save Your Sanity

About this Tutorial

How to change your locale and figure out what changing locales means for your sheets.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 Hello, better sheet members and non better sheet members. I'm probably gonna be putting this video on YouTube, so check it out if you're not a member yet. 00:08 Members got this video first. So become a member today. This is every locale listed in Google Sheets.

This is solving a problem that I have because a lot of people come to me for certain problems and then I try to create a little formula for them and they can't use it. 00:25 But it's also a you problem, and you might have this problem if you live in a country that is not well represented with blog posts or someone makes a blog post about a Google Sheets issue. 00:36 They create a Google Sheet formula , and then you copy and paste it into your sheet and it has the wrong dilemma. 00:44 So I'm gonna show you how you can fix that, how you can know if there's a problem.  And I created a little cool thing for you, but first, so first I'm gonna show you this data that I have, that I, I just discovered and, and quantified here. 01:01 Then second, I'm going to show you how to use this sheet that you can use this right away in the description below there will be a link to the sheet. 01:12 I do believe <laugh>.

All right, let's get started. So, locales, what are they? Well go up to file settings , and over here in it'll be locale . 01:21 You'll have settings for this sheet, and there's all these locales lo locations that you can have. And most of the time, most of the people watching this video are gonna have United States. 01:31 And their time zone is gonna be something like time zone doesn't really matter for this particular video, but just to show you what yours probably looks like is something where 's, where 's the USA times here? 01:44 Central time, mountain time, Eastern time.

Let's say Toronto, who, okay, save and reload. It's gonna reload. And I wrote a little script here and it should execute once it opens. 01:57 A one here will show me what the low cal code is, and that's what these are all here. It should change it. 02:03 There it goes, it changes it.

So if you do want to discover what your locale code is, you can do it two ways, right? 02:09 Go up to file settings , just look at what that United States, where that country is, and it'll be here like United States, or you can use this app script that I created here. 02:20 Let me load it up and show you. So what this does is on open, every time this sheet opens, it will find the locale , get spreadsheet locale here of the sheet, and then it will write it in a one. 02:31 It'll set that value in a one. So no, make sure you know that it's going go in a one.

So that's a little script I added there for you, but about this data . 02:41 So what happens when different, when a country is in a certain location, a few things are different. Inside of the formula , the separation between options or items is going to be at either a comma or a semicolon. 02:58 So in the United States, check it out. It's a, it's a comma. Also, the default date is gonna change either from months, days, years or days, months, years, or even there's a third one years, months, days. 03:14 You'll see here Canada has that China also, and Egypt also. And you have a few more.

The decimals will be different. 03:23 So in the United States period, in other countries it's comma and then also the thousands separator , the separation between like the one and the zero and 1000. 03:35 In the United States, it's a comma, but wherever it's the decimal is a comma. Then the thousand separator is a period. 03:44 You have one weird one, whereas I think it was Switzerland. Where is Switzerland? There we go. Switzerland has uses an apostrophe for their thousand separators. 03:55 So that's interesting to know. That's a weird one.

And that's what the differences are, right? So what happens is when you say you read a blog by someone in the United States or somewhere like, where 's India? 04:13 Here's India, similar to the United States. So if you are in a different country that has a different dilemma inside of your formulas and you need to make sure you change those commas to semicolons or vice versa, you might need to change the semicolon to a comma. 04:32 Well, how do you know which is which? How do you know what you, where you are? Well, you know where you are. 04:36 Where do you know where they are?

Well, you can usually get the location if you file, if you copy their, their sheet, you can find their location, how it's set, right? 04:46 File settings as I showed you before. Or you might just know, oh, that's a different string there. Sorry, not a different string , different limiter. 04:56 You might see that and see that in an error .

So here I created a way for you to change this. 05:01 So if you wanted to do something, if you want to first check, if you don't want to have to go through this whole list of 72 countries and different languages as well, you got English, French, got bangali, got a few different things, right? 05:18 Catalan, Spain, or Spain.

If you wanna check what if there is a difference like Argentina to Switzerland, yes, you do need a change. 05:26 And so if there's a change, I will show you this set of cells that allows you to put in the formula you have as a with a semicolon and you'll get the new formula here. 05:37 We use substitute, we use the substitute formula to revert all the semicolons to commas or vice versa.

So if you have something like the United States, which has a comma and you are in say, Argentina, it'll change the commas to semicolons for you just to put in the formula right here. 05:57 Here, we can take a look at that, can take this, put in the comma, we have to take away the equal size. 06:05 And there we go. We can copy this data right here. And that is change the comma to a semicolon. So I think this is a pretty cool tool. 06:17 At least this is a great tool for me, right?

Because I get a lot of questions from different people around the world. 06:22 A lot of different countries are members of FE sheets. But also if you end up, if, if you are in a different country than I am, I am mostly making my settings are gonna be in the United States. 06:36 And I don't really change, I won't change that setting by myself.

But if you then copy any formulas that I show you or share with you, you're going to have an error going to have something that looks like this. 06:52 So if we change this to a semicolon, you'll have error , you'll have a hashtag arrow formula , parse error .

That's the formula you're gonna get. 07:00 So if you see this formula parse error , many times it's not actually, it might not be just because you have the different dilemma. 07:09 It might be because you're missing some kind of parentheses if it might tell you if you have parentheses or you might have some other error . 07:16 But formula usually means there is a, a spelling error in the code. But if of course, if you have like a name error , it should tell you out there there's two equal signs here. 07:29 So that's what the form par error there.

And then it'll say name, okay, ette, this should be Nate Ette to Nate. 07:40 Okay? And then we can probably leave that blank and there is no error there. But if we add a semicolon, well if we add another string here and we put in a how about semicolon S fixed it for us there, it's fixing it for us. 08:08 Okay, finally got an error .

Again, formula parse error , the same parse error we've had before, I had a period there, we can just change that to a comma and we are fine. 08:17 So in looking in your locale and you're looking at maybe helpful blogs or YouTube videos that are trying to help you with other formulas or you're in our Facebook group , Facebook group , facebook.com/groups/i love Google Sheets. 08:32 A lot of people are trying to help each other and then they'll have an error whenever they copy and paste a formula . 08:37 This might be the reason why.

This is probably the most common reason why you might have an error if you're copy and pasting directly from online help forums Facebook groups or in my tutorials or even just switching spreadsheets. 08:54 But the thing is, if you download a spreadsheet and it is in say, this file settings of United States, it will change the formulas in here to your local one. 09:06 If you just go to file settings , we go, let's say let's do Switzerland, save and reload. It'll always reload.

And all of the formulas that are already well done, it will change. 09:19 And all of the formats, date formats, it'll change decimals, it'll change thousands sufferers. It will change automatically if it's correctly formatted to the other locale . 09:28 If you change locales, they'll correctly format . You won't get an error just by changing the locale . So if you do get an error when you change the locale , it probably was not correctly formatted before as well. 09:40 So that's some things to look out for, and I hope you enjoy this locale list.

I know this is a very weird little, tiny little problem that is solved. 09:48 Now we have now every single location in that, that Google Sheets allows. We have the dilemma, we have the default date format , the decimal, and the thousands separator . 09:58 I hope this has been really cool for you and please enjoy. Bye.</div>