Indian short currency format in google sheets

Question

In google sheets, I am trying to display numbers in Indian 'short' format with lakhs and crore suffix as follows:

Cell value: 1234, Display as 1.23K
Cell value: 12345, Display as 12.35K
Cell value: 123456, Display as 1.23L [L=lakh]
Cell value: 1234567, Display as 12.35L
Cell value: 12345678, Display as 1.23C [C=Crore]
Cell value: 123456789, Display as 12.35C.

I have tried to modify this custom number format:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

by Brook McEachern, but could not able to achieve my required format.

Does anyone know of a way to do this?

user_5632629 · Answer

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

user_5632629 · Answer

an alternative approach would be as follows...

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&""))

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