# Smart number formatting



## Jeffrey Mahoney (Wednesday at 3:33 PM)

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 (Wednesday at 4:19 PM)

Hi

*Format Personalizzed*
0,??
0"     "


----------



## Sufiyan97 (Wednesday at 4:30 PM)

Why don't you simply format column as General?


----------



## lrobbo314 (Wednesday at 4:38 PM)

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.xlsxAB1PIDResult2156156   3601601   4671671   512.4412.4461386.221386.2271388.11388.1 81471.11471.1 Sheet2Cell FormulasRangeFormulaB2:B8B2=A2&REPT(" ",3-LEN(TEXT(A2-TRUNC(A2),"@"))+1)


----------



## Joe4 (Wednesday at 4:57 PM)

Sufiyan97 said:


> 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 (Wednesday at 5:23 PM)

Sufiyan97 said:


> 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 (Wednesday at 5:25 PM)

Hi
*Format Personalizzed
0**.**?? *


----------



## Jeffrey Mahoney (Wednesday at 5:26 PM)

ISY said:


> Hi
> 
> *Format Personalizzed*
> 0,??
> 0"     "


Thank you.  I would like to not use Conditional formatting


----------



## Jeffrey Mahoney (Wednesday at 5:27 PM)

ISY said:


> Hi
> *Format Personalizzed
> 0**.**?? *


Thank you.  That shows the decimal character on whole numbers


----------



## Jeffrey Mahoney (Wednesday at 5:46 PM)

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}


----------

