Task:

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 SubThis 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!