google sheetsgoogle-sheets-settings
Is there a way to change settings of decimal separator, thousand separator and date format in Google Sheets?
I would like to have:
It is easy to set it up in Microsoft Excel. Can it be done in Google Sheets? I know that Google Sheets has an option to change locale. Is there any locale that has all the settings I want?
With the following custom number format, you are able to decimal-align numbers in Google Sheets, even by using fonts that use kerning.
???.?????
custom number format
result
I've added this solution in an example file: Decimal-Aligned Numbers
The following formula does the job (tested with U.S. and Russia as locales, where separators are different):
=IFERROR(VALUE(SUBSTITUTE(A2, ",", ".")), VALUE(SUBSTITUTE(A2, ".", ",")))
Explanation:
IFERROR returns the first argument unless it throws an error, in which case it returns the second. IFERROR is the result of converting the text, with any commas replaced by dots, into a number. IFERROR is the result of converting the text, with any dots replaced by commas, into a number. I'm assuming that in every locale, one of two things is going to work.
Best Answer
You need to go to:
→ spreadsheet settings
→ General
→ Locale
→ Change for a local that uses dot instead of comma
(Canada English for example)