2 Replies Latest reply on Dec 26, 2008 2:24 PM by Jens Teich

    Numbers to Text

    milky

      Title

      Numbers to Text

      Post

      Hi all,

       

      Merry Christmas,

       

      Is there a way to convert dollar amounts, i.e. $109.45 to show as:

      One hundred and Nine Dollars and Fourty Five Cents?

       

      With the following provisos:: 

      If there are no cents involved  e.g. $40.00 to show Fourty Dollars Only,

      and

      If no dollars are present show  e.g  $0.99 to show Ninety Nine Cents Only.

       

      Thanks to everyone who has helped in the past.

       

      Great Forum.

       

      Milton.

        • 1. Re: Numbers to Text
          stevestearns_1
            

          Hi Milton,

           

          Happy Holidays to you too. 

           

          Yes, there is a way to convert numbers to text as you describe. I found this many years ago and use it for my personal checkbook database. You will need the a No (or number field that you enter the amount into, 99.99) and several calculation fields. The spaces in the calculations are required, so don't delete any of them.

           

          No = Number

           

          Tn = Int(Mod(No;100))

           

          Hd = Int(Mod(No;1000) / 100)

           

          TTh = Int(Mod(No;1000000) / 1000)

           

          HT = Int(No / 100000)

           

          TnTx = "         One      Two      Three    Four     Five     Six      Seven    Eight    Nine     Ten      Eleven   Twelve   Thirteen Fourteen Fifteen  Sixteen  SeventeenEighteen Nineteen "

           

          TenTx = "              Twenty Thirty Forty  Fifty  Sixty  SeventyEighty Ninety "

           

          Cents = Int(No*100+.5) - Int(No)*100

           

          NumberText1 = If(HT;Trim(Middle(TnTx;HT*9+1;9)) & If(TTh;" Hundred ";" Hundred Thousand ");"") & If(TTh;If(GetAsNumber(TTh)>19;Trim(Middle(TenTx;Int(TTh/10)*7+1;7)) & " " & Trim(Middle(TnTx;Mod(TTh;10)*9+1;9));Trim(Middle(TnTx;TTh*9+1;9))) & " Thousand";"")

           

          NumberText2 = If(Hd;Trim(Middle(TnTx;Hd*9+1;9)) & " Hundred ";"") & If(GetAsNumber(Tn)>19;Trim(Middle(TenTx;Int(Tn/10)*7 +1;7)) & " " & Trim(Middle(TnTx;Mod(Tn;10)*9+1;9));Trim(Middle(TnTx;Tn*9+1;9)))

           

          NumberText3 = If(GetAsNumber(No)≥1 and GetAsNumber(Cents)>0;" and ";"") & If(GetAsNumber(Cents)>0;GetAsNumber(Cents)&"/100";"")

           

          Verbose = NumberText1 & " " & NumberText2 & NumberText3 & " Dollar" & If(GetAsNumber(No) ≠ 1;"s";"")

           

          FYI: The forum seems to be converting ") into a smily face is a quote and a close parenthesis.

           

          I hope this helps,