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

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.

Read it all here: https://blog.bettersheets.co/every-locale-listed-in-google-sheets-and-a-tool-to-save-your-sanity-2/