Google Sheets custom number format - Lakh and Crore suffix

Question

In google sheets, I need a way to display currency as follows:

Cell data: 2000000, Display: 20L
Cell data: 30000000, Display: 3Cr

Tried the "comma" formatter as explained here and here, but that does not work for my case. How can we get lakh/crore format?

I found this which works for excel, anything similar for google sheets?

this may need to be done with formula coz internal formatting is too weak for this — player0, Feb 14 '21 at 11:10
Does this answer your question? [Displaying Lakhs and Crores in Google Sheets](https://stackoverflow.com/questions/32359814/displaying-lakhs-and-crores-in-google-sheets) — Kessy, Feb 15 '21 at 14:46
@Kessy the answer you suggested, simply places the comma at the correct place according to the Indian currency system, I don't want that — Nitin Chaudhari, Feb 15 '21 at 15:07
Can you share then what is your need? Also, have you tried creating the custom format? — Kessy, Feb 22 '21 at 10:41
@Kessy i want to type 2000000 in A1 and 3 0000000 in A2, then I should be able to sum them in A3 with value as 32000000, however the display should be A1 -> 20L, A2 -> 3Cr and A3 -> 3.2 Cr I tried custom format but it only supports million, billion — Nitin Chaudhari, Feb 26 '21 at 15:01

player0 · Answer 1 · 2021-02-14T15:04:48.037

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, or ROUND can be completely removed, or it can be replaced by TRUNC if needed

spreadsheet demo

You have given a converter, it no longer has the required cell data. I only want the display to change, data should be intact - so that I can continue to use the cell data in other operations like sum() — Nitin Chaudhari, Feb 15 '21 at 12:13

score 0 · Answer 2 · answered Mar 01 '21 at 13:39

0

You can create a Feature Request on Google's Issue tracker so they can implement this feature on sheets.

To do so, you have to go to the Sheets API component and then create a new Feature Request

answered Mar 01 '21 at 13:39

Kessy

1,894
1
8
15

score 0 · Answer 3 · answered Nov 26 '21 at 20:19

here is an improoved alternative approach...

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)&"×"&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"), "(\.)$", ), 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&""))

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