IS there a way to decimal aliegn in Excel

[Guest]

 

[JE McGimpsey]

See "About custom number formats" in XL Help.

For instance:

Format/Cells/Number/Custom 0.0???

will display at least 1 but no more than 4 places after the decimal
point, aligned.


"lynn" <[email protected]> wrote:

 

[Jack Sons]

JE,

With 0.???? an tnteger will diisplayed as e.g. 3. or 78. How for an integer
(only with integers) to get rid of the decimal point and keeping the LSD in
its place as if the decimal point also was in its place?
So we would get
3
0.1245
7.8
with the 3 right above the 0. But 3.00001 shopuld be displayed as
3.0000
Is that possible?

Jack Sons
The Netherlands

 

[JE McGimpsey]

I believe that in order to align on the decimal point, the decimal point
has to be visible.

One could use an event macro to format the cell:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const dEPSILON As Double = 1e-10
Dim rArea As Range
Dim rCell As Range
For Each rArea In Target.Areas
For Each rCell In rArea.Cells
With rCell
If Abs(.Value - Fix(.Value)) < dEPSILON Then
.NumberFormat = "0_._0_0_0"
Else
.NumberFormat = "0.0??"
End If
End With
Next rCell
Next rArea
End Sub


You could limit the entries to specific cell(s) if desired.

 

[Jack Sons]

JE,

Very nice, works super, thanks a lot.

Jack.