Change
How can change number to text. For example amount in invoice is 5000usd . I will see text too in down invoice. Five thousand dollars
70 replies
-
format(number, format mask) - Converts a number into a formatted text.
https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language
-
I know number but i dont know what is format mask. Can you example me?
-
This information is available at the link I posted above. If you go to that link and type command+f, your browser should popup a search box where you can enter the search term; format in this case.
Examples:
| format(42.5, "0") => "42"
| format(42.5, "000") => "042"
| format(42.5, "000.00") => "042.50"
| format(42.5, "0.00") => "42.50"
| format(42.5, "#,##0.00") => "42.50"
| format(1042.5, "#,##0.00") => "1,042.50" -
Thank you
-
There is your example. 42.5=>42.
i want 42=>forty two
For example: total in invoice is 5000usd i will add one formula that show five thousand dollars
The link dont explaine about this.
-
I apologize, I misunderstood your original post. That would require some custom coding. I'm sure that's possible, but it would require a time commitment that I can't make right now. Maybe someone else will jump in and help.
-
Thank you
-
Yep, this got stuck in my head. This works for whole numbers upto 9,999.
let arySingles := ["", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"];
let aryMultiples := ["", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"];
let txtNumber := format(Number, "0");
let numDigits := length(txtNumber);
let numIndex := 0;
let txtNumberWord := "";
let i := numDigits;
while i > 0 do
switch i do
case 4:
(
numIndex := number(substr(txtNumber, 0, 1));
txtNumberWord := item(arySingles, numIndex) + " Thousand"
)
case 3:
if substr(txtNumber, numDigits - i, 1) != 0 then
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex) + " Hundred"
end
case 2:
if substr(txtNumber, numDigits - i, 1) != 0 then
if substr(txtNumber, 2, 1) = 1 then
numIndex := number(substr(txtNumber, numDigits - i, 2));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex);
i := 0
else
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(aryMultiples, numIndex)
end
end
case 1:
if substr(txtNumber, numDigits - i, 1) != 0 then
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex)
end
end;
i := i - 1
end
;
txtNumberWordYou can paste this to a formula field and plug the field name into this line where you see "Number".
let txtNumber := format(Number, "0");
-
Thank you very much.
can you explaine me how can use this formula? For example invoice amount is 30.000usd how can see your formula. Thank you for your cooperation.
-
It is currently only able to display numbers upto 9,999. When I get time, I'll try to update for larger numbers. Add a formula field to your table, copy the code and paste it to the formula field.
You will have to replace "Number" with the name of your field as I described at the end of my previous post.
-
Thank you very much
-
I replace number to net. Net is net total invoice, but dont work
Do you can see my screenshot clear?
-
“Number” in my case is a number field. I’m not sure if you can use a formula field in this situation. Try this, add a variable above “txtNumber” that is equal to the same formula you use in your “Net” formula. Don’t use the field “Net”, but instead, it’s formula.
-
Wait! The only place you replace “Number” is in the line,
let txtNumber := format(Number, “0”);
You only replace it in that one location. You are replacing the number() function and you can’t do that.
-
After you have restored the number() functions, try
let txtNumber := format(Net,
“0”); -
I have confirmed this works with a formula field. All you have to do is copy and paste.
let arySingles := ["", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"];
let aryMultiples := ["", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"];
let txtNumber := format(Net, "0");
let numDigits := length(txtNumber);
let numIndex := 0;
let txtNumberWord := "";
let i := numDigits;
while i > 0 do
switch i do
case 4:
(
numIndex := number(substr(txtNumber, 0, 1));
txtNumberWord := item(arySingles, numIndex) + " Thousand"
)
case 3:
if substr(txtNumber, numDigits - i, 1) != 0 then
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex) + " Hundred"
end
case 2:
if substr(txtNumber, numDigits - i, 1) != 0 then
if substr(txtNumber, 2, 1) = 1 then
numIndex := number(substr(txtNumber, numDigits - i, 2));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex);
i := 0
else
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(aryMultiples, numIndex)
end
end
case 1:
if substr(txtNumber, numDigits - i, 1) != 0 then
numIndex := number(substr(txtNumber, numDigits - i, 1));
txtNumberWord := txtNumberWord + " " + item(arySingles, numIndex)
end
end;
i := i - 1
end
;
txtNumberWord -
Yes, i have one field with name ‘deposit’ and it is number field. I changed name to number and do your formula and it is working and correct.
-
Yes, your formula worked and great. Thank you for your cooperation.
One question; for example there is “three hundred seventy” how can you the last it “usd’. I want be :three hundred seventy usd.
-
Change the last line from,
txtNumberWord
to
txtNumberWord + " usd"
-
Great. Thank you
-
Whenever you can continue to write this formula is good. Eventually up to a hundred thousand. Of course there is a problem. and formula can not calculate point. for example: 2000.45$
thank you.
-
The large numbers definitely highlighted the shortcomings of my solution so I did some searching online. I found a very nice solution here,
http://www.techiedelight.com/c-program-convert-number-to-words/
and translated it to be used in Ninox. I had to make some changes so that it would work with western numbering and it will convert numbers upto 9,999,999,999.49. I had some fun with the user defined functions and commenting. The only change you have to make is here,
let numAmount := YourField;
Here is the code for the formula field,
let empty := "";
let x := [empty, "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen "];
let y := [empty, empty, "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety "];
"/* ";
" * User defined functions";
" */ ";
function mod(dividend : number,divisor : number) do
dividend - divisor * floor(dividend / divisor)
end;
function makeCentsToMe(num : number) do
if contains(text(num), ".") then
let dpIndex := index(text(num), ".");
"and " + substr(text(num), dpIndex + 1, 2) + "/100 usd"
end
end;
function numberToWord(num : number,suffix : text) do
if floor(num) != 0 then
if num > 19 then
item(y, floor(num / 10)) + item(x, mod(num, 10)) + suffix
else
item(x, num) + suffix
end
else
empty
end
end;
"/* ";
" * Main program";
" */ ";
"// Number to be converted to words";
let numAmount := Amount;
"// Test for number of digits";
if length(format(numAmount, "0")) < 11 then
"// String to store word representation of given number";
let NtW := "";
"// This handles Cents";
NtW := makeCentsToMe(numAmount);
"// This handles digits at Ones and Tens place";
NtW := numberToWord(mod(numAmount, 100), "") + NtW;
"// This handles digit at Hundreds place";
NtW := numberToWord(mod(numAmount / 100, 10), "Hundred ") + NtW;
"// This handles digits at One Thousands and Ten Thousands place";
NtW := numberToWord(mod(numAmount / 1000, 100), "Thousand ") + NtW;
"// This handles digit at Hundred Thousands place";
NtW := numberToWord(mod(numAmount / 100000, 10), "Hundred ") + NtW;
"// This handles digits at One Millions and Ten Millions place";
NtW := numberToWord(mod(numAmount / 1000000, 100), "Million ") + NtW;
"// This handles digit at Hundred Millions place";
NtW := numberToWord(mod(numAmount / 100000000, 10), "Hundred ") + NtW;
"// This handles digit at One Billions place";
NtW := numberToWord(mod(numAmount / 1000000000, 10), "Billion ") + NtW;
NtW
else
"Number is too large"
end -
I do but dont work.
-
change the first line to
let Amount := Net;
-
let numAmount := Amount;
is already in the formula. All you have to do is replace "Amount" with "Net".
Content aside
-
2
Likes
- 7 mths agoLast active
- 70Replies
- 16625Views
-
1
Following