|
|
How do you convert a numerical value to the equivalent written text?
To convert a field called origNumber to written text, use the field formula:
REM "Thanks to MStevenson@symantec.com for updating this for negative numbers";
REM "Get numerical value";
origNumber := <numerical value>;
REM "Convert negative to positive";
NUMBER := @If(origNumber = "";0;origNumber < 0;-origNumber;origNumber);
REM "Test range";
@If(NUMBER > 999999999999999; @Return("Quadrillion Plus!"); "");
REM "Parse, format fixed with 2 decimals";
money := @Text(NUMBER; "F2");
c := @Word(money; "."; 2);
d := @Right("000000000000000" + @Word(money; "."; 1); 15);
REM "Constants";
n := "" : "One" : "Two" : "Three" : "Four" : "Five" : "Six" : "Seven" :
"Eight" : "Nine" : "Ten" : "Eleven" : "Twelve" : "Thirteen" :
"Fourteen" : "Fifteen" : "Sixteen" : "Seventeen" : "Eighteen" :
"Nineteen";
m := "" : "" : "Twenty " : "Thirty " : "Forty " : "Fifty " : "Sixty " :
"Seventy " : "Eighty " : "Ninety ";
REM "Ones"; d0 := @Middle(d; 12; 3); h0 := @Subset(@Subset(n; @TextToNumber(@Left(d0; 1)) + 1); -1); v0 := @TextToNumber(@Right(d0; 2)); t0 := @Trim(@If(h0 = ""; ""; h0 + " Hundred ") + @Subset(@Subset(m; 1 + @TextToNumber(@Middle(d0; 1; 1))); -1) + @Subset(@Subset(n; @If(v0 > 19; @TextToNumber(@Right(d0; 1)); v0) + 1); -1));
REM "Thousands"; d1 := @Middle(d; 9; 3); h1 := @Subset(@Subset(n; @TextToNumber(@Left(d1; 1)) + 1); -1); v1 := @TextToNumber(@Right(d1; 2)); t1 := @Trim(@If(h1 = ""; ""; h1 + " Hundred ") + @Subset(@Subset(m; 1 + @TextToNumber(@Middle(d1; 1; 1))); -1) + @Subset(@Subset(n; @If(v1 > 19; @TextToNumber(@Right(d1; 1)); v1) + 1); -1));
REM "Millions"; d2 := @Middle(d; 6; 3); h2 := @Subset(@Subset(n; @TextToNumber(@Left(d2; 1)) + 1); -1); v2 := @TextToNumber(@Right(d2; 2)); t2 := @Trim(@If(h2 = ""; ""; h2 + " Hundred ") + @Subset(@Subset(m; 1 + @TextToNumber(@Middle(d2; 1; 1))); -1) + @Subset(@Subset(n; @If(v2 > 19; @TextToNumber(@Right(d2; 1)); v2) + 1); -1));
REM "Billions"; d3 := @Middle(d; 3; 3); h3 := @Subset(@Subset(n; @TextToNumber(@Left(d3; 1)) + 1); -1); v3 := @TextToNumber(@Right(d3; 2)); t3 := @Trim(@If(h3 = ""; ""; h3 + " Hundred ") + @Subset(@Subset(m; 1 + @TextToNumber(@Middle(d3; 1; 1))); -1) + @Subset(@Subset(n; @If(v3 > 19; @TextToNumber(@Right(d3; 1)); v3) + 1); -1));
REM "Trillions"; d4 := @Left(d; 3); h4 := @Subset(@Subset(n; @TextToNumber(@Left(d4; 1)) + 1); -1); v4 := @TextToNumber(@Right(d4; 2)); t4 := @Trim(@If(h4 = ""; ""; h4 + " Hundred ") + @Subset(@Subset(m; 1 + @TextToNumber(@Middle(d4; 1; 1))); -1) + @Subset(@Subset(n; @If(v4 > 19; @TextToNumber(@Right(d4; 1)); v4) + 1); -1));
REM "Put it together"; @If(origNumber = "";"";origNumber < 0;"Negative ";"") + @Trim(@If(t0 + t1 + t2 + t3 + t4 = ""; "Zero"; @If(t4 = ""; ""; t4 + " Trillion ") + @If(t3 = ""; ""; t3 + " Billion ") + @If(t2 = ""; ""; t2 + " Million") + @If(t1 = ""; ""; t1 + " Thousand ") + t0)) + " Dollars and " + @If(c = ""; "00"; c) + " Cents"