How to format numbers with a different thousand separator in Google Sheets?

Question

On "Format->Number->More formats->Custom number format" there are several presets, and you can create your own. Great.

So #,##0.0 yields results such as 1,234.6, ok interesting.

But I don't want the ,, I want to use the ' as a thousand separator.

Seems simple enough, right? #'##0.0 pretty logical, just change the symbol and it should just work.

Result: horrific monsters like '331.0. Like, any number with no thousands is prepended with a '. Same exact syntax but with the , and it works as expected.

PS: I've tried \' just in case but no.

Answer 1

I see this while searching for ways to correctly display "k/m/b" for thousand/million/billion in google sheets. I tried the similar way, which can give correct result for numbers less than a billion. Although it looks messy but I'll explain.

Solution for integer < 1,000,000,000:

[<1000]#,##;[>=1000000]#"'"###"'"###;#"'"###

A Simple demo, as you can see the number goes wrong when it gets too large

The second column I can share here as well:

[<1000000]#,##0.0," k";[>=1000000000]#,##0.0,,," b";#,##0.0,," m"

This one shows correct result between 0.1k to 1000b

Short Explaination: The two ";" work like this:

[First rule]format1; [Second rule]format2; [Others]format3

Because of the original design of the number rule in spread sheet, there can't be more than two rules. By utilizing the "others", we can create 3 number ranges: [<10e3], [>=10e6], [10e3 ~ 10e6]

Than for the format part is easy

#,## is the natural format for number less than 1000

#"'"### applies only for 1000 ~ 1000000

#"'"###"'"### for number greater than 1000000

Reference:

https://www.benlcollins.com/spreadsheets/google-sheets-custom-number-format/

https://scripteverything.com/how-to-format-numbers-in-thousands-k-in-google-sheets/