3

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?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
icru
  • 31
  • 1
  • Blog post links to this SO post: https://stackoverflow.com/questions/19573706/how-to-use-unit-abbreviation-in-excel#_=_ – General Grievance Apr 26 '21 at 19:05
  • Closest I can get you is this: `[<999950]0.00,"K";[<9999500]0.00,,"L";0.00,,,"C"%%`, but that leaves trailing `%` signs. Sadly, I don't think there's a way to get rid of the percent signs in the output. Unless somebody knows some obscure trick that I don't? – General Grievance Apr 26 '21 at 19:52

2 Answers2

0

enter image description here

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

player0
  • 124,011
  • 12
  • 67
  • 124
0

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

enter image description here

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

enter image description here

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

enter image description here

  • 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

english demo sheet

non-english demo sheet

player0
  • 124,011
  • 12
  • 67
  • 124