Forum Discussion

SayaliJoshi's avatar
SayaliJoshi
Qrew Member
4 years ago

Numbers in Words - Indian Currency

I want figures in words as per Indian Currency.
I have used the below formula. The problem with the formula is it is reading 400000 as 'Four lakh Thousand Rupees' whereas it should be 'Four Lakh Rupees(4,00,000)' and for 120000 it is reading correctly as 'One Lakh Twenty Thousand Rupees'. Also if the figure is 84.65 then ideally it should be "Eighty Rupees and Sixty Five Paise' and not 'Eighty Rupees and Sixty-Five cents'.

Formula -

var text numt = ToText(Floor([Total Material Cost (INR)]));

var text cents = If(Contains(ToText([Total Material Cost (INR)]), "."), Left(Right(ToText([Total Material Cost (INR)]) & "00", "."), 2),"");

var text teensnum = List(" ",

Case(Floor(ToNumber(Right($numt,2))/10),

1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),

Case(ToNumber(Right($numt,1)),

1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));

var text teens =

If($teensnum<>"" and [Total Material Cost (INR)]>99, "and ", "") &

Case($teensnum,

"Ten One", "Eleven", "Ten Two", "Twelve", "Ten Three", "Thirteen", "Ten Four", "Fourteen", "Ten Five", "Fifteen", "Ten Six", "Sixteen", "Ten Seven", "Seventeen", "Ten Eight", "Eighteen", "Ten Nine", "Nineteen", $teensnum);
var text teensnumcents =List(" ",

Case(Left($cents,1),

"1", "Ten", "2", "Twenty", "3", "Thirty", "4", "Forty", "5", "Fifty", "6", "Sixty", "7", "Seventy", "8", "Eighty", "9", "Ninety", "0", ""),

Case(Right($cents,1),

"1", "One", "2", "Two", "3", "Three", "4", "Four", "5", "Five", "6", "Six", "7", "Seven", "8", "Eight", "9", "Nine", "0", "")

);
var text teenscents =

If($teensnumcents<>"", " and ", "") &

Case($teensnumcents,

"Ten One", "Eleven", "Ten Two", "Twelve", "Ten Three", "Thirteen", "Ten Four", "Fourteen", "Ten Five", "Fifteen", "Ten Six", "Sixteen", "Ten Seven", "Seventeen", "Ten Eight", "Eighteen", "Ten Nine", "Nineteen", $teensnumcents)& If($teensnumcents<>"", " Cents", "");

List(" ",

If([Total Material Cost (INR)]>=1000000,

Case(Floor([Total Material Cost (INR)]/1000000),

1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine") & " Million","") & If(Mod([Total Material Cost (INR)],1000000)>0 and [Total Material Cost (INR)]>1000000, ",", ""),

If([Total Material Cost (INR)]>=100000,

Case(Floor(ToNumber(Right($numt,6))/100000),

1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine") & " Lakh","") & If(Mod([Total Material Cost (INR)],100000)>0 and [Total Material Cost (INR)]>100000, ",", "") ,

If([Total Material Cost (INR)]>=10000,

Case(Floor(ToNumber(Right($numt,5))/10000),

1, Case(Floor(ToNumber(Right($numt,4))/1000),0,"Ten" ,1,"Eleven",2,"Twelve",3,"Thirteen",4,"Fourteen",5,"Fifteen",6,"Sixteen",7,"Seventeen",8,"Eighteen",9,"Nineteen")
, 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),""),

If([Total Material Cost (INR)]>=10000 and [Total Material Cost (INR)]<20000," Thousand",If([Total Material Cost (INR)]>=1000,

Case(Floor(ToNumber(Right($numt,4))/1000),

1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, "") & " Thousand","") & If(Mod([Total Material Cost (INR)],1000)>0 and [Total Material Cost (INR)]>1000, ",", "")),

If([Total Material Cost (INR)]>=100,

Case(Floor(ToNumber(Right($numt,3))/100),

1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, ""),""),

$teens)
& " Rupees" & $teenscents

------------------------------
Sayali Joshi
------------------------------
Formulas and functions
  • AmedhaSurve1's avatar
    AmedhaSurve1
    Qrew Member
    3 years ago
    You are my Favorite Human

    ------------------------------
    Amedha Surve
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      3 years ago
      I've created something which seems to work fine for us
      • It ain't pretty , and can be optimised big time towards the end
      • Doesn't work for more than 99 crore (yet)

      var number numt = [Number]; // Define your field 

      var number crore=ToNumber(Right(ToText(If($numt>9999999,Floor($numt/10000000))),2)); // Extract Crore if they exists
      var number lakhs=ToNumber(Right(ToText(If($numt>99999,Floor($numt/100000))),2));
      var number thousands = ToNumber(Right(ToText(If($numt>999,Floor($numt/1000))),2));
      var text hundreds = If($numt>99,Left(Right(ToText(Floor($numt)),3),1));
      var text tens = If($numt>1,ToText(ToNumber(Right(ToText(Floor($numt)),2))));
      var text fraction = If(not IsNull(Frac($numt)),ToText(Frac($numt)*100));


      // ----- Crore Begins ---- \\
      If($crore>10 and $crore<20, Case($crore, 11,"Eleven", 12,"Twleve",13,"Thirteen",14,"Fourteen",15,"Fifteen",16,"Sixteen",17,"Seventeen",18,"Eighteen",19,"Ninteen"),

      Case(Floor($crore/10), 1, "Ten", 2, "Twenty ", 3, "Thirty ", 4, "Forty ", 5, "Fifty ", 6, "Sixty ", 7, "Seventy ", 8, "Eighty", 9, "Ninety ", 0, "")
      & Case(Mod($crore,10), 1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""))

      & If(ToText($crore)="","",$crore=0,""," Crores ") &

      // ----- Lahs Begins ---- \\

      If($lakhs>10 and $lakhs<20, Case($lakhs, 11,"Eleven", 12,"Twleve",13,"Thirteen",14,"Forteen",15,"Fifteen",16,"Sixteen",17,"Seventeen",18,"Eighteen",19,"Ninteen"),

      Case(Floor($lakhs/10), 1, "Ten", 2, "Twenty ", 3, "Thirty ", 4, "Forty ", 5, "Fifty ", 6, "Sixty ", 7, "Seventy ", 8, "Eighty", 9, "Ninety ", 0, "")

      & Case(Mod($lakhs,10), 1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""))

      & If(ToText($lakhs)="","",$lakhs=0,""," Lakhs ")&

      // ----- Thousand Begins ---- \\
      If($thousands>10 and $thousands<20, Case($thousands, 11,"Eleven", 12,"Twleve",13,"Thirteen",14,"Forteen",15,"Fifteen",16,"Sixteen",17,"Seventeen",18,"Eighteen",19,"Ninteen"),

      Case(Floor($thousands/10), 1, "Ten", 2, "Twenty ", 3, "Thirty ", 4, "Forty ", 5, "Fifty ", 6, "Sixty ", 7, "Seventy ", 8, "Eighty", 9, "Ninety ", 0, "")

      & Case(Mod($thousands,10), 1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""))

      & If(ToText($thousands)="",""," Thousand ") &

      Case($hundreds,"1", "One Hundred ", "2", "Two Hundred ", "3", "Three Hundred ", "4", "Four Hundred ", "5", "Five Hundred ", "6", "Six Hundred ", "7", "Seven Hundred ", "8", "Eight Hundred ", "9", "Nine Hundred ", "0", "") &

      /// - X TENS //
      // ---X tens Sequence Being X ---- /
      If(
      $tens= "1", "One",
      $tens= "2", "Two",
      $tens= "3", "Three",
      $tens= "4", "Four",
      $tens= "5", "Five",
      $tens= "6", "Six",
      $tens= "7", "Seven",
      $tens= "8", "Eight",
      $tens= "9", "Nine",
      $tens= "11","Eleven",
      $tens= "12","Twleve",
      $tens= "13","Thirteen",
      $tens= "14","Fourteen",
      $tens= "15","Fifteen",
      $tens= "16","Sixteen",
      $tens= "17","Seventeen",
      $tens= "18","Eighteen",
      $tens= "19","Ninteen",
      $tens= "10", "Ten",
      $tens= "20", "Twenty",
      $tens= "30", "Thirty",
      $tens= "40", "Forty",
      $tens= "50", "Fifty",
      $tens= "60", "Sixty",
      $tens= "70", "Seventy",
      $tens= "80", "Eighty",
      $tens= "90", "Ninety",
      Case(Floor(ToNumber($tens)/10), 2, "Twenty ", 3, "Thirty ", 4, "Forty ", 5, "Fifty ", 6, "Sixty ", 7, "Seventy ", 8, "Eighty ", 9, "Ninety ") &

      Case(Right(ToText($tens),1), "1", "One", "2", "Two", "3", "Three" , "4", "Four", "5", "Five", "6", "Six", "7", "Seven", "8", "Eight", "9", "Nine", "0", ""))&


      // ---X Fraction Sequence Being X ---- /
      If(
      $fraction= "1", " & One Paisa Only",
      $fraction= "2", " & Two Paisa Only",
      $fraction= "3", " & Three Paisa Only",
      $fraction= "4", " & Four Paisa Only",
      $fraction= "5", " & Five Paisa Only",
      $fraction= "6", " & Six Paisa Only",
      $fraction= "7", " & Seven Paisa Only",
      $fraction= "8", " & Eight Paisa Only",
      $fraction= "9", " & Nine Paisa Only",
      $fraction= "11"," & Eleven Paisa Only",
      $fraction= "12"," & Twleve Paisa Only",
      $fraction= "13"," & Thirteen Paisa Only",
      $fraction= "14"," & Fourteen Paisa Only",
      $fraction= "15"," & Fifteen Paisa Only",
      $fraction= "16"," & Sixteen Paisa Only",
      $fraction= "17"," & Seventeen Paisa Only",
      $fraction= "18"," & Eighteen Paisa Only",
      $fraction= "19"," & Ninteen Paisa Only",
      $fraction= "10", " & Ten Paisa Only",
      $fraction= "20", " & Twenty Paisa Only",
      $fraction= "30", " & Thirty Paisa Only",
      $fraction= "40", " & Forty Paisa Only",
      $fraction= "50", " & Fifty Paisa Only",
      $fraction= "60", " & Sixty Paisa Only",
      $fraction= "70", " & Seventy Paisa Only",
      $fraction= "80", " & Eighty Paisa Only",
      $fraction= "90", " & Ninety Paisa Only",

      Case(Floor(ToNumber($fraction)/10), 2, " & Twenty ", 3, " & Thirty ", 4, " & Forty ", 5, " & Fifty ", 6, " & Sixty ", 7, " & Seventy ", 8, " & Eighty ", 9, " & Ninety ") &

      Case(Right(ToText($fraction),1), "1", "One Paisa Only", "2", "Two Paisa Only", "3", "Three Paisa Only" , "4", "Four Paisa Only", "5", "Five Paisa Only", "6", "Six Paisa Only", "7", "Seven Paisa Only", "8", "Eight Paisa Only", "9", "Nine Paisa Only", "0", ""))
      ​

      ------------------------------
      Prashant Maheshwari
      ------------------------------