Hi,

I've been using Geoffrey Gerhard's FormatNumberAsText custom function (http://www.briandunning.com/cf/871) for quite a while and have found it very handy. It accepts a number as input and outputs a text string formatted to a specified number of decimals (among other useful things). In my solution, I've always specified 8 digits for the number of decimals and had no problem, but a recent project I've needed to specify 10 digits. With that precision, the function sometimes returns a number in scientific notation rather than decimal - for example 0..0e+107440 when I was hoping for 0.0000107440. 

I've looked at the function to see if there's anything I could change to make it always return a decimal rather than a scientific number, but frankly the logic is over my head. I was wondering if anyone here might have a suggestion.

Thanks,

Tom

Here's the function:

/*
Function:
FormatNumberAsText ( number ; currency ; separator ; decimal ; precision )

Parameters:
number = the number you want to format
currency = the string that identifies the currency or is a prefix -
optional
separator = the punctuation that breaks up big numbers - optional
decimal = the punctuation that separates integer digits from fraction digits
          It defaults based on FMP's localization unless rounding is <= 0,
          in which case it's omitted
precision = number of places for rounding - defaults to 2 if parameter is
          not a digit or is unspecified

Purpose:
This function formats numbers for use in text strings or calculations with a
text result. It's especially useful when there are multiple numbers in a
text string and at least one must be displayed in a different format than
the others.

Notes:
Could expand to include colorization parameter (for negative numbers)

Could expand to include choice of negative symbology for numbers < 0

Could expand to include trailing/leading parameter for currency notation

*/
Let ( [
t = Int ( GetAsNumber ( precision ) ) ;
prec = Case ( Length ( t ) ; t ; 2 ) ;
dec = Case ( Length ( decimal ) ; decimal ; Filter ( 1/2 ; ".," ) ) ;
n = Round ( number + 0 ; prec ) ;
sgn = Case ( n < 0 ; "-" ) ;
n = Abs ( n );
x = NumToJText ( Int ( n ) ; 1 ; 0 ) ;
y = NumToJText ( n * 10^prec ; 0 ; 0 )
] ;

sgn & currency
& Substitute ( x ; "," ; separator )
& Case (
prec > 0 ;
dec & Right ( 10^prec & y ; prec )
)

)

The CF works for me. Are you using the Text Result or Number??

 

Hi Lee,

Thanks for your reply. In my solution, I'm calling the CF and passing a number, and the result is written to a text field in a virtual list. The attached file is representative. My test data is "1/6000" for the expression (evaluated using the Evaluate ( ) function); if precision is set as 8 you get "0.00016667", which is correct. If you set it to ten, you get "0.+101666667", which I assume is the correct number is scientific format, but the result I want is "0.0001666667".

Thanks,

Tom

CF_test.fmp12

Change the global Results to a Number Result and see if that does it for you.

Add the following variable definition:

pad = Substitute ( 10^prec - 1 ; "9" ; "0" )

then replace this line:

dec & Right ( 10^prec & y ; prec )

with:

dec & Right ( pad & y ; prec )

 

Comment - perfect! Thank you so much. That did the trick.