1

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

68 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    format(number, format mask) - Converts a number into a formatted text.

     

    https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

    • New_Sun
    • 5 yrs ago
    • Reported - view

    I know number but i dont know what is format mask.   Can you example me?

    • Sean
    • 5 yrs ago
    • Reported - view

    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"

    • New_Sun
    • 5 yrs ago
    • Reported - view

    Thank you

    • New_Sun
    • 5 yrs ago
    • Reported - view

    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.

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

    • New_Sun
    • 5 yrs ago
    • Reported - view

    Thank you

    • Sean
    • 5 yrs ago
    • Reported - view

    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
    ;
    txtNumberWord

     

    You can paste this to a formula field and plug the field name into this line where you see "Number".

     

    let txtNumber := format(Number, "0");

    • New_Sun
    • 5 yrs ago
    • Reported - view

    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.

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

     

    Screen Shot 2018-10-30 at 6.42.36 AM

     

    You will have to replace "Number" with the name of your field as I described at the end of my previous post.

    • New_Sun
    • 5 yrs ago
    • Reported - view

    Thank you very much

    • New_Sun
    • 5 yrs ago
    • Reported - view

    I replace number to net. Net is net total invoice, but dont work

     

    9FAAF3FA-B0FA-47EB-BD6A-57BBD565DF5D

    7E3918DD-16BC-45E2-B507-A5AC3CF3184D

     

    Do you can see my screenshot clear?

    • Sean
    • 5 yrs ago
    • Reported - view

    “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.

    • Sean
    • 5 yrs ago
    • Reported - view

    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.

    • Sean
    • 5 yrs ago
    • Reported - view

    After you have restored the number() functions, try

     

    let txtNumber := format(Net, “0”);

    • Sean
    • 5 yrs ago
    • Reported - view

    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

    • New_Sun
    • 5 yrs ago
    • Reported - view

    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. 

    • New_Sun
    • 5 yrs ago
    • Reported - view

    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.

    • Sean
    • 5 yrs ago
    • Reported - view

    Change the last line from,

     

    txtNumberWord

     

    to

     

    txtNumberWord + " usd"

    • New_Sun
    • 5 yrs ago
    • Reported - view

    Great. Thank you

    • New_Sun
    • 5 yrs ago
    • Reported - view

    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.

    • Sean
    • 5 yrs ago
    • Reported - view

    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

    • New_Sun
    • 5 yrs ago
    • Reported - view

    I do but dont work. 

    • blackie
    • 5 yrs ago
    • Reported - view

    change the first line to

    let Amount := Net;

    • Sean
    • 5 yrs ago
    • Reported - view

    Screen Shot 2018-11-03 at 7.13.33 AM

     

    let numAmount := Amount; is already in the formula. All you have to do is replace "Amount" with "Net".

Content aside

  • 1 Likes
  • 3 yrs agoLast active
  • 68Replies
  • 16492Views