How can I get the text of a number in the way one would speak it? For example $100.00 = One Hundred Dollars

  • 0
  • 3
  • Question
  • Updated 4 weeks ago
  • Answered

I could do it with this formula!


var text numt = ToText(Floor([Number]));

var text cents = If(Contains(ToText([Number]), "."), Left(Right(ToText([Number]) & "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 [Number]>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([Number]>=1000000,

Case(Floor([Number]/1000000),

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

If([Number]>=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") & " Hundred","") & If(Mod([Number],100000)>0 and [Number]>100000, " and", "") ,

If([Number]>=10000,

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

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

If([Number]>=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([Number],1000)>0 and [Number]>1000, ",", ""),

If([Number]>=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)


& " Dollars" & $teenscents


Photo of Sam

Sam

  • 20 Points

Posted 4 years ago

  • 0
  • 3
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
Thx for sharing.  I will save that for my next Cheque writing app.
Photo of Andrew

Andrew

  • 0 Points
As usual ... you are da man!
Photo of rahul

rahul

  • 50 Points
Hi Sam, I am using the above formula but i noticed one issue if we give single digit number 2 then its returning "and two dollors" :(
Example - Number = 11600 then formula is giving value "Ten One Thousand, Six hundread dollars" ideally it should say "Eleven thousand and Six hundread dollars" ? i am trying to fix it but would be great if you can tell me which variable is causing this issue?

It would be great help if you can provide us the upgraded formula if you have. or atleast can guide me where to make the changes to achieve the goal :)
Photo of Ursula Ll

Ursula Ll

  • 1,634 Points 1k badge 2x thumb
I noticed this as well, did you get any update on this?
Photo of Ursula Ll

Ursula Ll

  • 1,634 Points 1k badge 2x thumb
I made some modifications to this calculation so that it could update the teens. I am SURE there are better ways to do this, but I needed it quickly and I didn't want to waste any time cleaning it up. Posting here in case someone else needs it. 

var number Value = Round([xxTHENUMERICFIELDxx],0.01);
var text Hundreds=Right(ToText(Int($Value)),3);
var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
var text Millions = If($Value>=1000000,ToText(Int($Value/1000000)));

var text numt = ToText(Floor([xxTHENUMERICFIELDxx]));

var text cents = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."), Left(Right(ToText([xxTHENUMERICFIELDxx]) & "00", "."), 2),"");
var text centDecimals = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."),Right(ToText(Int($value * 100)),2));

var text teensnumcents = If($centDecimals<>"", " and " & List("/",
$centDecimals,"100")," and No/100") & " Dollars" ;

var text firstNum = 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, "");
var text secondNum = 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 thirdNum = 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, "");
var text firstThouNum = If($Thousands<>"",Case(Floor(ToNumber(Right($Thousands,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondThouNum = If($Thousands<>"",Case(ToNumber(Right($Thousands,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdThouNum = Case(Floor(ToNumber(Right($Thousands,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, "");
var text firstMilNum = If($Millions<>"",Case(Floor(ToNumber(Right($Millions,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondMilNum = If($Millions<>"",Case(ToNumber(Right($Millions,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdMilNum = Case(Floor(ToNumber(Right($Millions,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, "");

var text tensNum = List("-", $firstNum, $secondNum);
var text thouNum = List("-", $firstThouNum, $secondThouNum);
var text millNum = List("-", $firstMilNum, $secondMilNum);

var text teensCon = If(Contains($tensNum,"ten-")=true,Case($tensNum,
"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", $tensNum),$tensNum);

var text thouCon = If(Contains($thouNum,"ten-")=true,Case($thouNum,
"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", $thouNum),$thouNum);

var text millCon = If(Contains($millNum,"ten-")=true,Case($millNum,
"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", $millNum),$millNum);

var text teens = $teensCon;
var text Thouteens = If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999, " ", "") & $thouCon;
var text Milteens = If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, " ", "") & $millCon;

var text hundredsNum = $thirdNum &" "& $teens;
var text hundredsThouNum = $thirdThouNum &" "& $thouCon;
var text hundredsMillNum = $thirdMilNum &" "& $millCon;

var text finalHund = $hundredsNum; 
var text finalThou = $hundredsThouNum & " Thousand";
var text finalMill = $hundredsMillNum & " Million";

//var text finalThouOne = if(Contains($finalThou,"Ten-",$Thouteens,$finalThou));


If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, $finalMill, "")&" "&
If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999,$finalThou, "")&" "&
If($tensNum<>"" and [xxTHENUMERICFIELDxx]>99, $finalHund, $teens) & 
if(nz([xxTHENUMERICFIELDxx])<>0,$teensnumcents,"")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,308 Points 50k badge 2x thumb
Thank you for posting!
Photo of Ursula Ll

Ursula Ll

  • 1,634 Points 1k badge 2x thumb
no worries, Sam did all the heavy lifting for this!