Discussions

Numbers in Words - Indian Currency

  • 1.  Numbers in Words - Indian Currency

    Posted 12 days ago
    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
    ------------------------------