Displaying Lakhs and Crores in Google Sheets
Google SheetsGoogle Sheets Problem Overview
I would like to use Google Sheets for its convenient global access, but I really need to display numbers in Lakh and Crore (Indian style) format to avoid confusion among managers.
150,000
should display as 1,50,000
and 12,000,000
should display as 1,20,00,000
.
I tried setting the format to ##,##,##,000
but my number still displays as 12,000,000
. I couldn't find anything in the docs.
Does anyone know of a way to accomplish this?
Google Sheets Solutions
Solution 1 - Google Sheets
This works in Google Sheets for sure. Should work in Excel too.
With the appropriate cells selected, navigate to:
Google Sheets : Format -> Number -> More Formats -> Custom Number Format
Excel : Format -> Cells -> Custom -> [Custom text box]
Enter one of the following and Apply:
A] For Lakhs and Crores with the Rupee symbol and decimals
[$₹][>9999999]##\,##\,##\,##0.00;[$₹][>99999]##\,##\,##0.00;[$₹]##,##0.00
B] For Lakhs and Crores with the Rupee symbol and without decimals
[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0
C] For Lakhs and Crores without the Rupee symbol and with decimals
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
D] For Lakhs and Crores without both the Rupee symbol and decimals
[>9999999]##\,##\,##\,##0;[>99999]##\,##\,##0;##,##0
Edit: I've tested these on both Google Sheets and Excel. Negative numbers work as well.
Solution 2 - Google Sheets
Indian currency format displays numbers like below
1 - One Rupee
10 - Ten Rupee
100 - One Hundred Rupee
1,000 - One Thousand Rupee
10,000 - Ten Thousand Rupee
1,00,000 - One Lac Rupee
10,00,000 - Ten Lac Rupee
1,00,00,000 - One Crore Rupee
So below formats seems to be correct.
To show positive and negative INR/Rs. up to Lakh
[>99999][$₹]##\,##\,##0.00;[<-99999][$₹]##\,##\,##0.00;[$₹]##\,##0.00
To add support for positive lakhs and crores
[>9999999][$₹]##\,##\,##\,##0.00;[>99999][$₹]##\,##\,##0.00;[$₹]##,##0.00
Add support for negative lakhs and crores
[<-9999999][$₹]##\,##\,##\,##0.00;[<-99999][$₹]##\,##\,##0.00;[$₹]##,##0.00
For Microsoft excel, add the above format at
Format -> Cells -> Custom -> [Custom text box]
Below screenshot demonstrates above custom format in use:
Reference: https://www.raghunayak.com/2020/07/how-to-show-inrrs-in-lakh-crore-format.html
Solution 3 - Google Sheets
Note: Please refer to the accepted answer to display lakhs and crores with proper commas. This answer only adds the Rs. symbol to the number while the comma syntax remains as it is.
As of Dec 2020, Google Sheets has added Indian Rupee formatting as well. Just go to File -> Spreadsheet settings -> Locale
and select India.
Let it refresh, you will find Rs.
symbol in the toolbar.
Solution 4 - Google Sheets
new formula approach:
- works with numeric numbers
- works with plain text numbers
- works with text
- works with empty cells
- works with negative values
- works with zeros
- works with scientific notations
- works with decimals
- works up to Shankh
- works up to 10^±50
indian separator system:
=INDEX(IF(IFERROR(N(ABS(A1:A*1)))>0, REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
REPT(0, 50)&"×"&TEXT(A1:A, "0"), REPT("(..)", 24)&"(...)$",
JOIN(",", "$"&SEQUENCE(25))), "(.*×,?)", ), "-,", "-")&
IFNA(REGEXEXTRACT(A1:A&"", "(\.\d{1})")), A1:A&""))
indian short currency:
=INDEX(IF((IFERROR(N(ABS(A1:A*1)))>0)+(IF(ISBLANK(A1:A),,IFERROR(A1:A*1, 1)=0)),
REGEXREPLACE(TEXT(TRUNC(IFNA(A1:A*(10^-(
VLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")),
SEQUENCE(8, 1, 6, 2), 1)-1)), A1:A), 1), "0.#"), "(\.)$", )&" "&
HLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), {0, SEQUENCE(1, 8, 4, 2);
SPLIT("Rp♦Rp♦L♦Cr♦Arab♦Kharab♦Nil♦Padma♦Shankh", "♦")} , 2), ""&A1:A))
both indian systems combined:
=INDEX(IF((IFERROR(N(ABS(A1:A*1)))>0)+(IF(ISBLANK(A1:A),,IFERROR(A1:A*1, 1)=0)),
REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
REPT(0, 50)&"×"®EXREPLACE(TEXT(TRUNC(IFNA(A1:A*(10^-(
VLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")),
SEQUENCE(8, 1, 6, 2), 1)-1)), A1:A), 1), "0"), "(\.)$", ), REPT("(..)", 24)&"(...)$",
JOIN(",", "$"&SEQUENCE(25))), "(.*×,?)", ), "-,", "-")&
IFNA(REGEXEXTRACT(A1:A&"", "(\.\d{1})"))&" "&
HLOOKUP(LEN(TEXT(INT(ABS(A1:A*1)), "0")), {0, SEQUENCE(1, 8, 4, 2);
SPLIT("Rp♦Rp♦L♦Cr♦Arab♦Kharab♦Nil♦Padma♦Shankh", "♦")} , 2), A1:A&""))
english demo sheet
non-english demo sheet
Solution 5 - Google Sheets
unfortunately, internal formatting is able to work only with 3 types (see more here) if you need to get more from indian currency system you can use a formula like below either for separator system or short currency system respectively
indian separator system in B2:
=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(
REGEXEXTRACT(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(IFERROR(
REGEXEXTRACT(A2:A, REPT("(.)", IF(LEN(A2:A)=3, LEN(A2:A)-4, LEN(A2:A)-3))), "0"),
"select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX(LEN(A2:A)-3)), 1, )))),,9^9)), " ", ),
"(.{2})", "$1,"), REPT("(.)", IF((LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)<1, 1,
(LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)))),
"select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX((LEN(A2:A)-3)+
ROUNDDOWN((LEN(A2:A)-3)/2))), 1, )))),,9^9)), " ", ), "^,", )&","&IFNA(
REGEXEXTRACT(A2:A, "...$"), IF(A2:A="",,TEXT(A2:A, "000"))), "^0,$", ))
indian short currency in C2:
=ARRAYFORMULA(IFNA(ROUND(A2:A*VLOOKUP(LEN(A2:A), {SEQUENCE(19),
{1; 1; 1; 1; 1; 10^-5; 10^-5; 10^-7; 10^-7; 10^-9; 10^-9;
10^-11; 10^-11; 10^-13; 10^-13; 10^-15; 10^-15; 10^-17; 10^-17}}, 2, 1), 2)&" "&
VLOOKUP(LEN(A2:A), {SEQUENCE(19),
{"Rp"; "Rp"; "Rp"; "Rp"; "Rp"; "L"; "L"; "Cr"; "Cr"; "Arab"; "Arab";
"Kharab"; "Kharab"; "Nil"; "Nil"; "Padma"; "Padma"; "Shankh"; "Shankh"}}, 2, 1)))
- side note:
ROUND
is set to 2 decimal places. this can be set to 0, orROUND
can be completely removed, or it can be replaced byTRUNC
if needed