Microsoft KB Archive/23942

Multiplan: Suppressing Leading Zeros PSS ID Number: Q23942 Article last modified on 01-17-1991 PSS database name: D_MPlan

3.00 3.01 3.02 3.04 4.00 4.01 4.20

MS-DOS

Problem:

I want to suppress the generation of leading zeros in the presentation of decimal equivalents of fractional numbers for values less than 1. For example, the value .25 would be displayed as .25, not as 0.25.

Response:

Leading zeros can be suppressed, but the process is not without a catch. In your example, the value 0.25 can be transformed into .25 by nesting Multiplan’s FIXED function inside the MID function. The syntax would be as follows:

MID(FIXED(cell reference,2),2,3)

The FIXED function rounds a value to a specific number of decimal places, then converts it to text. The MID function returns a specified number of characters. In the above example, the MID function is instructed to begin “getting” characters with the second character (that is, the “.” in 0.25). It is to continue getting characters until it has returned three characters.

The drawback to this process (for which there is no workaround) is that the set of characters must remain in text. To mathematically manipulate the item, it must be converted back into a value by using Multiplan’s VALUE function. In this example, .25 will revert back to 0.25 if it is subjected to the VALUE function. Please note that Multiplan is not causing this problem. The appended leading zero, applied to decimal equivalents with a value of less than one, is a system-level default over which Multiplan has no control.

Copyright Microsoft Corporation 1991.