Jump to content

Modifying FormatNumberAsText fn to suppress scientific notation

Tom R.

This topic is 2299 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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.



Here's the function:

FormatNumberAsText ( number ; currency ; separator ; decimal ; precision )

number = the number you want to format
currency = the string that identifies the currency or is a prefix -
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

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.

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 )


Link to comment
Share on other sites

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




Link to comment
Share on other sites

This topic is 2299 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.