Smart number formatting

[Jeffrey Mahoney]

I have a need to display numbers that are actual Project IDs aligned at the decimal. Some of the PIDs have varying digits before and after the decimal. Could be 3.0, 3.2, 4.0, 4.1, 4.2. I can accomplish this using conditional formatting, but that is cumbersome. Conditional formatting also has a tendency to replicate and mess things up.

Rules:
* No leading or trailing zeros
* Align values at expected decimal
* Whole numbers can't show the decimal
* Use only number formatting

Has anybody accomplished this?

 

[Jeffrey Mahoney]

So I learned a new thing a couple of days ago about Number Formatting (NF). You can have criteria other than Positive, Negative, Zero, and Text. You can specify if the value is less than or greater than any number, AND have multiple criteria. So, based on the rules in the first post:
* No leading or trailing zeros
* Align values at expected decimal
* Whole numbers can't show the decimal
* Use only number formatting I wasn't able to do it without Conditional Formatting (CF).
Here is one method:
* Left Alignment
* NF for all: [<1000]?###.??;General 'Less than 1000 apply space + 3 digits + maybe 2 digits
* CF Formula and NF if true
- =AND(O5<1000,MOD(O5,1)=0) 'Less than 1000 and a whole number
- ?### 'Space + 3 digits and no decimal

The cool part is specifying that you want to make the NF a certain formatting if the number less than 1000, and then General if not true.

My wish is that I could specify a NF rule to apply a number formatting where the number is a whole number, like this:
[WHOLE]?###;General