@ > Home > Contents > No decimal separator if #,##0.??? is an integer Excel 97+

Applying user defined format #,##0.??? you get the following:

```    4.567
34.56
234.5
1,234.    <----!```

The remaining decimal separator has to be erased..

Solution (also see "Better solution" later):

F1: 3 (or another count of maximum decimals)

B2: =SUBSTITUTE(TEXT(A2,"#,##0."&REPT("?",\$F\$1)),"."&REPT(" ",\$F\$1),REPT(" ",\$F\$1+1))

resulting in:

```    4.567
34.56
234.5
1,234```

The cell (or column) also must be formatted (1) aligned right and (2) with a fix spaces font, as Courier, Fixedsys, Terminal, Simplified Arabic Fixed or Miriam Fixed.

C10: =SUMPRODUCT(--B2:B9)-SUM(A2:A9) is a possible check whether the string is calculated right.

Note: If the <space> (ASCII 32) and the <period> had the same proportion as a digit, proportional fonts would also work.

2 special currency formats:

```    4.57
34.56
234.50
1,234
1,234.--```

B2:
=SUBSTITUTE(TEXT(A2,"#,##0.00"),".00","   ") and
=SUBSTITUTE(TEXT(A2,"#,##0.00"),".00",".--")

Better solution (inspired by Remo Grütter and Andreas Killer 090221, German newsgroup):

```Sub fmtBrushing()

rng = "B1:C100"
dec = 2
Range(rng).NumberFormat = "#,##0." & String(dec, "0")
fmt = "#,##0_." & WorksheetFunction.Rept("_0", dec)

For Each Z In Range(rng)
If IsNumeric(Z) And Len(Z) > 0 Then _
If Z = Int(Z) Then _
Z.NumberFormat = fmt
Next Z

End Sub```
This simple code pre-formats a given range (rng) with the "normal" format "#,##0.00". Then it checks for integer values in it, applying the desired decimal-fraction-less format aligned. It both a) works fine with proportional fonts and b) will not need an extra =TEXT()-column!