Int() confusion

[BobWeaver]

I remember encountering this problem a couple of years ago, and I know that I used a different method at that time. I have a feeling that I may have ended up using a case function similar to Ernst's.

[-Queue-]

Okay, try this one.

Choose(Sign(num) + 1, "-") & Abs(Int(num)) & "." & Left(Substitute(Round(Mod(Abs(num),1),3),".","") & "000",3)

[ernst]

Sorry Queue,

This one goes wrong with inputs between 0.9995 en 0.9999 (positive & negative)

It think that's because of the integer function that you use.

I attached a database with a digest of the suggested methods so far. All calculations have been adjusted to use 'comma' as decimal separator.

And for those who think that this subject is trivial: if you want to include a formatted number in a text field then this function is really a must. And prefarably without error...

regards,

Ernst.

NumToTextDigest.fp5.zip

[BobWeaver]

How 'bout this:

Left("-",-Sign(Num)) &Middle(10^Int(1+Log(Max(1,Abs(Num))))+.0001 +Round(Abs(Num),3),2,5+Log(Max(1,Abs(Num))))

I'm trying to see if I can do it without any logical functions, and this is just about as illogical as you can get.

[ernst]

My computer got overheated but this one works like a charm! What a genius you are, Bob...

Now let's see who can come up with the least efficient way to do this.

My approach will be to just make one big database which looks up the right answer in a table.

Dont fear, I wont post it...

[Ugo DI LUCA]

God yes Bob is a God

Thanks for this elegancy and logical approach. Ernst, good idea your digest...

[BobWeaver]

Wow, my head is swelling already!

I really only posted that last one as a joke. It uses 13 functions. I think I would stick with Ernst's formula which only uses 9, unless someone can do it with fewer than that.

[Ugo DI LUCA]

I left it here :

Case(Left(Num,1) = "-","-")& Choose(Left(Abs(Num),1),"0") & Abs(Round(Num,3)) & Case(Patterncount(Num,","),

Choose(Length(Round(Num,3)) - Position(Round(Num,3), ",", 1, 1), "000", "00", "0"),",000").

14 functions.

[Ugo DI LUCA]

This later wasn't working neither on values kind of 12,9999.....

Bob, yours doesn't work neither with values of type 99,9999 giving 0,000 instead of 100,000

Another try so I can end this week-end with a semi-workable calc

Case(Num<0, "-")& Case(Left(Abs(Num),1)=".","0") & Abs(Round(Num, 3)) &

Case(Patterncount(Round(Num,3), "."),

Choose(Length(Num) - Position(Num, ".", 1, 1), "000", "00", "0"),".000")

12 functions....

I guess Ernst is the most robust *anyway*....

[BobWeaver]

I'm not surprised that my last one fails with large numbers, because it appends a 1 on the left and right side of the number to ensure that when the middle function trims off the first and last characters, there will be the correct number of digits including a leading zero if necessary. This reduces the total accuracy of the number by at least 2 significant digits.

[djgogi]

This one will put thousand separator too

Case( NUMBER < 0 , "-")

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^9 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^9 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^6 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^6 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^3 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^3 )) , 3 ) & "," )

&

Right( Abs( Int( Round( NUMBER , PRECISION ))) , 3 )

&

Choose( PRECISION = 0 , "." &

Right( 10^PRECISION & Abs( Round( NUMBER , PRECISION ) )

* ( 10^PRECISION ) , PRECISION ))

NUMBER and PRECION are numbers (no kidding)

Dj