Lotus Notes FAQ Visit Our Sponsor!

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"

Applies to Notes Versions: 3
4
4.5
4.6
5
Last Modified: August 5, 1998