In Google Sheets, I want to decimal-align numbers without adding trailing zeros in the fractional part.
Instead of: I want: +------+ +------+ | 56.0| | 56 | | 27.0| | 27 | | 83.0| | 83 | | 2.4| | 2.4| | 1.2| | 1.2| | 120.0| | 120 | +------+ +------+
The format of the first column is easily accomplished (for example, using the format “0.0” or just setting custom decimal points under the 123 button).
In Excel, I believe the format of the second column is accomplished using the format “0.?”, but as of this writing it does not work in Google Sheets.
It is possible to force trailing characters with Substitute:
= Substitute( Text( formula ; "0.0" ) ; ".0" ; " ") = Substitute( Text( formula ; "0.0" ) ; ".0" ; "__" )
Note: the two substituted whitespace characters are not U+0020 SPACE but U+2002 EN SPACE. (U+00A0 NO-BREAK SPACE also works.)
Some undesirable side effects of this workaround are that it
- modifies not just the display format but also the value of the cell
- requires the use of a monospaced font in the cell
- trailing whitespace causes font substitution due to the Unicode and does not print correctly
- trailing underlines are not as desirable as trailing whitespace
- confuses maintainers because it is not evident that an EN SPACE is being used
- (presumably) fails in locales which use the period as the period separator (thousands separator) instead of the decimal marker
With the following custom number format, you are able to decimal-align numbers in Google Sheets, even by using fonts that use kerning.
I’ve added this solution in an example file: Decimal-Aligned Numbers