Eliminating trailing zeros in decimal numbers

Tuesday 26 August 2014 @ 11:31 am

A user in one of my LinkedIn groups had an unusual requirement.  He needed to eliminate trailing zeros from a series of decimal numbers.  The numbers could go out to 4 or more decimal places and he didn’t want to show any trailing zeros.  He wanted a column to look like this:


instead of this


There are other approaches but I think this is the simplest. Just put your field in the first line of this formula:

Local NumberVar x := {YourField};
Local NumberVar y := Length (Totext (Truncate (Val (StrReverse (Totext (x,9)))),0,''));
Totext (x,y)

Note that this will work with numbers up to 9 decimals. You can increase the number of decimals in the second line by replacing the number 9 with a larger number.

The above example is a formula that returns a string. Another approach is to take the original numeric field and go into:
[ Format >> Field >> Number tab >> Customize button ]
Then put the following line into the condition button of the decimal places property. You don’t even need the name of the field:

Length (Totext (Truncate (Val (StrReverse (Totext (CurrentFieldValue,9)))),0,''))

 This is simpler because it doesn’t involve writing a formula field and it also doesn’t change the data type to ‘string’.  It can also be applied to any field without modification. The only downside is that having the logic hidden in a conditional property may mean that it is forgotten.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

One Response to 'Eliminating trailing zeros in decimal numbers'

  1. Ken Hamady - September 8th, 2014 at 6:50 am

    This comment from Bob Antaki:

    “When I want to condition an item on the report with logic, instead of putting the logic into the condition, most of the time I first create a regular formula, and put the logic in that regular formula. Then I just put the new formula name into the condition logic. I give a descriptive name to the new formula, and this makes it less likely that I will forget about the logic condition.”

    I use the same technique myself. In this case the only downside is that we lose the ability to use the “CurrentFieldValue” function. That allows you to paste the formatting to multiple fields without having to change the logic. If we wanted to use this logic in 10 fields we would have to choose between writing 10 different formula fields or using the expression above within the field formatting.

Leave a Reply

Recrystallize Pro