How do I get Google Sheets to display lakhs correctly?

Question

I would like Google Sheets to format 1,00,000 as I wrote it, and not as 100,000. How do I do that? I tried the following number formats mentioned I found via Googling, and none of them work:

##,###

##,##0

#,##,##0.0

What's the right format? And what do the symbols in this string mean, anyway?

Answer 1

From the answer by Adam Starrh on Stack Overflow, pointed out by w3dk:

[>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##0

correctly formats integers up to 99,99,99,999. Here's an explanation:

• 0 means a digit that has to be there, even if it's an insignificant 0.
• # means a digit that only appears when it's significant.
• Concerning separating commas, the only thing Google Sheets care about is whether you want them, not where you want them. If the number is 1000000 and the format is ####### there are no commas; if the format is #####,## (for example), then the commas are inserted in traditional places: 1,000,000.
• So, the workaround used above is to escape commas with backslashes, so they are treated as regular characters. This allows one to place them manually. The downside of manual placement is that one has to keep track of how many digits the number has and adjust the number of commas accordingly. This is why there are three different formats above:
  • ##\,##\,##\,##0 for numbers >9999999
  • ##\,##\,## for numbers >99999
  • ##,##0 for numbers 0-99999

If you need numbers greater than 999999999, add another condition in front:

[>999999999]##\,##\,##\,##\,##0;  (rest as before)