Google Sheets Locales. What are they?

Hello, Better Sheet members and non-Better Sheet members! This tutorial is about every locale listed in Google Sheets.

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, they create a Google Sheet formula and then you copy and paste it into your sheet and it has the wrong delimiter. How do you resolve that?

I'm going to show you how you can fix that and how you can know if there's a problem.

Locales. What are they in Google Sheets?

In the sheet, go up to File > Settings. The locale will be there.

You'll have settings for this sheet and there’s all these locations that you can choose from.

Most of the time, most of the people watching this video are going to have United States.

Time zone doesn't really matter for this particular video, but let me just to show you what it probably looks like.

For this tutorial, let’s go with Eastern Time – Toronto.

Hit “Save and Reload” button. Your sheet should reload.

I wrote a little script and it should execute. Once it opens, A1 will show me what the locale code is. That’s what these are in column A.

How do you discover your locale code using Google Sheets?

If you do want to discover what your locale code is, you can do it two ways:

First: Go to File > Settings and just look at what the country is under “Locale.”

In our tutorial, it shows “United States.”

It should show in the Country column, which is B.

Second way: You can use the Apps Script that I created.

What this does is every time this sheet opens, it will find the locale, which is:

SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale()

And then it’ll set that value in A1.

Delimiters and errors in Google Sheets

So what happens is 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 a either a comma or a semicolon.

The default date is going to change, too. It could be month, date , year or date, month, year. It could even be year, month, date.

The decimals will be different (period or comma), and also the thousands separators (comma, period, space, and even apostrophe).

If you are in a different country that has a different delimiter inside of your formulas and you need to make sure you change those commas to semicolons or vice versa, how do you know which is which?

You can go to File > Settings and look at what the country is under “Locale”, like what I showed you before.

Or you might just know that’s a different string there with a different delimiter. You might see that error.

Getting the correct formula

So here I created a way for you to change this.

Let’s say you want to first check, but you don't want to have to go through this whole list of 72 countries and different languages.

What do you do?

If you want to check if there is a difference between Argentina and Switzerland, you’ll find out that yes, you need to change something.

I will now show you this set of cells that allows you to put in the formula you have (i.e. semicolon) and then you’ll get a new formula.

We use the substitute formula to revert all the semicolons to commas or vice versa.

If you have something like the United States, which has a comma and you are in Argentina, it'll change the commas to semicolons for you.

Just to put in the formula from column C of the “Locale List” tab.

CONCATENATE(“string”,“string”)

Copy and paste that formula in B6 (“Change Countries” tab).

Then we can get your new formula in D6.

I think this is a pretty cool tool!

How do I avoid getting an error?

I’m mostly making my settings in the United States. My settings are going to be in the United States and I don't really change that by myself. But if you then copy any formulas that I show you or share with you, you're going to have an error.

Example: If we change the delimiter to semicolon, you’ll get a parse error.

If you see this formula parse error, it might not be just because you have the different delimiter. It might be because you're missing some kind of parenthesis.

The formula parse error usually means there is a spelling error in the code. But if of course, if you have like a name error, it should tell you

This is probably the most common reason why you might have an error: If you're copy and pasting directly from online help forums and Facebook groups or in my tutorials or even just switching spreadsheets.

But the thing is, if you download this spreadsheet and it has the settings of United States, it will be able to change the formulas in here to your local one.

If you just go to File > Settings, try Switzerland, save, and reload. You’ll see that all of the formulas are already done. It will change the data.

It will change automatically if it's correctly formatted to the other locale. If you change locales, they'll correctly format. You won't get an error just by changing the locale.

If you do get an error when you change the locale, it probably was not correctly formatted before as well. So that are some things to look out for.

I hope you enjoyed this tutorial on locale list!

Watch the video version:

Check out other popular tutorials in Better Sheets:

Get more Google Sheets Tutorials at BetterSheets.co

We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets. For only $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.