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?

 

[ISY]

Hi

Format Personalizzed
0,??
0" "

 

[Sufiyan97]

Why don't you simply format column as General?

 

[lrobbo314]

This works, but the results need to be in a monotype font like "Lucida Console".

You can't really see it line up below, but after you apply one of those fonts it will look right.

2023 Work Exercise.xlsx
	A	B
1	PID	Result
2	156	156
3	601	601
4	671	671
5	12.44	12.44
6	1386.22	1386.22
7	1388.1	1388.1
8	1471.1	1471.1
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=A2&REPT(" ",3-LEN(TEXT(A2-TRUNC(A2),"@"))+1)

 

[Joe4]

Why don't you simply format column as General?

If some numbers have decimals and some numbers don't (and they don't always have the same number of decimals), they will not line up on the decimal point if you just use the General format.
(Take a look at the Result column in the original post -- that is what he is after).

 

[Jeffrey Mahoney]

Why don't you simply format column as General?

Because the expected decimals don't line up when there is a number like 893.9 and 1289.14

 

[ISY]

Hi
Format Personalizzed
0.??

 

[Jeffrey Mahoney]

Hi

Format Personalizzed
0,??
0" "

Thank you. I would like to not use Conditional formatting

 

[Jeffrey Mahoney]

Hi
Format Personalizzed
0.??

Thank you. That shows the decimal character on whole numbers

 

[Jeffrey Mahoney]

This is the way I currently achieve the goal:
Standard number formatting: ####.??
Conditional formatting: ####_._?_? With this formula: =A6=INT(A6)

I was hoping not to use conditional formatting

{this has to be right justified}