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
Does this answer your question? Displaying Lakhs and Crores in Google Sheets — Kessy
@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
Can you share then what is your need? Also, have you tried creating the custom format? — Kessy
@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

player0 player0 · Accepted Answer · 2021-02-14T14:36:46

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

Google Sheets custom number format - Lakh and Crore suffix

2 Answers

indian separator system in B2:

indian short currency in C2:

spreadsheet demo