Number to Text

[Ricardo]

Hi All,

Ok so im not sure if im just having a non-brain working day!!...but i have a number field where users enter the number of items they require.

I want to then below this show the number in text fromat.

E.g. Number field the users enters 10000

I would like the text calculation field to show 'Ten Thousand'...is this possible?...

Thanks.

[Orlando]

Hi Ricardo

I have come across a custom function that is just what you are looking for.

It can be found on Brian Dunning's web site:

http://www.briandunning.com/cf/106

Hope this helps

Orlando

[Ricardo]

this custom function doesnt work!! Ihave marked 'FAILS HERE' where it fails...anyne any ideas...im just having a bad day!!!

Case(NumberIn = 0; ""; Let ( [FullNumber = GetAsNumber(numberin); Number = Int(FullNumber); len = Int(Log(Number)) + 1] ;

Case(len > 15; "Out of Range";

Currency = "EvalOnly"; Let([Hundred = Left(number; 1); TensOnes = Right(number; 2); TONum = GetAsNumber(TensOnes); Tens = Left(TensOnes; 1); Ones=If(TONum < 20; TensOnes; Right(TensOnes; 1))];

Case(len = 3; Choose(hundred; ""; "One"; "Two"; "Three"; "Four "; "Five"; "Six"; "Seven"; "Eight"; "Nine") & " Hundred" & Case(TONum≠0;" ")) & Case(TONum > 19; Choose(tens; ""; ""; "Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety") & Case(Ones ≠ 0; " "))

& Choose(ones; ""; "One"; "Two"; "Three"; "Four "; "Five"; "Six"; "Seven"; "Eight"; "Nine"; "Ten"; "Eleven"; "Twelve"; "Thirteen"; "Fourteen"; "Fifteen"; "Sixteen"; "Seventeen"; "Eighteen"; "Nineteen")

);

If(len >3; Let([useLeft = Choose(Mod(len; 3);3; 1; 2); NewLen= 10 ^ (len - UseLeft); ThisValue = Div(number; NewLen); NewValue = Number - ThisValue * NewLen]; NumberInWords FAILS HERE(ThisValue; "EvalOnly"; "None") & " " & Choose(Div(len - 1; 3); ""; "Thousand"; "Million"; "Billion"; "Trillion" ) & Case(NewValue ≠ 0;" " & NumberInWords(NewValue; ""; "None")));

NumberInWords(Number; "EvalOnly"; "None")) & Case(currency ≠ ""; " " & Currency) & Case(DecimalCurr ≠ "None"; " and " & Let([Decimal = Int(100 * (fullnumber - number))]; If(Decimal = 0; "Zero"; NumberInWords(Decimal; "EvalOnly"; "None"))) & " " & DecimalCurr))))

[Genx]

Are you one hundred percent sure you named the function NumberInWords - because it is calling itself at this point - other than that i highly doubt it doesn't work - it was published in the brian dunning newsletter

[Genx]

Also, i just tried it and it works fine.

[Ricardo]

I copied and pasted the NumberInWords to make sure!

I get the alert:

An Operator(e.g +,-,*,...) i s expected here!

i cant see where i need to insert an operator...only 1 hour and 15 minutes until home time...i dont need this on a friday!!! :)

[Genx]

No clue then sorry, the only thing i could think of was mis-spelling, as i said it worked perfectly fine for me.

[Ricardo]

Can you do me a screen shot of your custom function for this...if yours works then maybe im doing somehting wrong!!?

Thansk in advance.

[Genx]

Sure :)

[Ricardo]

cheers Genx...got it working...typo!! :P