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

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.

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: