0

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.

1
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

enter image description here

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.