S
Steve Gerdemann
Is it possible to allign an excel work sheet so that
numbers like 32.2, 1.2 & .002 all align on the decimal
point
Thanks
Steve Gerdemann
numbers like 32.2, 1.2 & .002 all align on the decimal
point
Thanks
Steve Gerdemann
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Wilson
Steve,
Format/Cells/Number/Accounting
John
Format/Cells/Number/Accounting
John
D
Dave Peterson
Give the range a custom format like:
#,##0.??????
(as many trailing question marks as you want.)
#,##0.??????
(as many trailing question marks as you want.)
J
John Wilson
Dave,
Wouldn't
Format/Cells/Number/Accounting
do what he was looking for without having to custom format??
John
Wouldn't
Format/Cells/Number/Accounting
do what he was looking for without having to custom format??
John
D
David McRitchie
Hi Steve,
I have some examples on my Formulas page
http://www.mvps.org/dmcritchie/excel/formula.htm
Align on decimal point
0.????_);(0.????);0.????;@
--
I have some examples on my Formulas page
http://www.mvps.org/dmcritchie/excel/formula.htm
Align on decimal point
0.????_);(0.????);0.????;@
--
John Wilson said:Dave,
Wouldn't
Format/Cells/Number/Accounting
do what he was looking for without having to custom format??
John
D
Dave Peterson
I think it would depend on if the OP wanted extra 0's after the last digit:
32.200
1.200
0.002
(with no currency mark)
With the custom format (#,##0.???), I got:
32.2
1.2
0.002
I guess it depends on what Steve really wanted.
32.200
1.200
0.002
(with no currency mark)
With the custom format (#,##0.???), I got:
32.2
1.2
0.002
I guess it depends on what Steve really wanted.
D
David
Dave Peterson wrote
And by replacing 0 with #, results were as OP posted:
32.2
1.2
.002
Steve, are you listening?
With the custom format (#,##0.???), I got:
32.2
1.2
0.002
And by replacing 0 with #, results were as OP posted:
32.2
1.2
.002
I guess it depends on what Steve really wanted.
Steve, are you listening?
J
Jack Sons
Dave,
Related question:
I would like to align
500
58,0
32.2
1.2
0.002
on the decimal point so the result will be
500
58
32.2
1.2
0.002
Hope even after sending over internet you will see what I mean; from top to
bottom the 0 the 8 the 2 the 1 and the leftmost 0 are in line.
The especially difficult part (for me) is that of integers not only zero's
to the right of the decemal point should not show, but also the decimal
point itself should not show.
With custom formats like (#,##0.???) I can't get rid of the decimal point in
case of integers.
Is possible what I want?
Jack Sons
The Netherlands
Related question:
I would like to align
500
58,0
32.2
1.2
0.002
on the decimal point so the result will be
500
58
32.2
1.2
0.002
Hope even after sending over internet you will see what I mean; from top to
bottom the 0 the 8 the 2 the 1 and the leftmost 0 are in line.
The especially difficult part (for me) is that of integers not only zero's
to the right of the decemal point should not show, but also the decimal
point itself should not show.
With custom formats like (#,##0.???) I can't get rid of the decimal point in
case of integers.
Is possible what I want?
Jack Sons
The Netherlands
D
Dave Peterson
I don't think you can do this with numberformat.
(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)
Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)
If the values were the results of formulas, you could use the
worksheet_calculate event.
Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)
Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)
If the values were the results of formulas, you could use the
worksheet_calculate event.
Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Jack said:Dave,
Related question:
I would like to align
500
58,0
32.2
1.2
0.002
on the decimal point so the result will be
500
58
32.2
1.2
0.002
Hope even after sending over internet you will see what I mean; from top to
bottom the 0 the 8 the 2 the 1 and the leftmost 0 are in line.
The especially difficult part (for me) is that of integers not only zero's
to the right of the decemal point should not show, but also the decimal
point itself should not show.
With custom formats like (#,##0.???) I can't get rid of the decimal point in
case of integers.
Is possible what I want?
Jack Sons
The Netherlands
J
Jack Sons
Thaks Dave, I will try.
Jack.
Jack.
Dave Peterson said:I don't think you can do this with numberformat.
(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)
Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)
If the values were the results of formulas, you could use the
worksheet_calculate event.
Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
J
Jack Sons
Dave,
I changed the decimal case as follows (
'decimal
Target.NumberFormat = "########0.??"
one ? less for only two decimals)
It appears that
30,004 results in 30, should be 30
30,4 results in 30,4 should be 30,40
If indeed the values were the results of formulas, should
If Target.HasFormula Then Exit Sub
be changed in a lead to another (new) part of this sub that is adapted to
work with the results of formulae and using worksheet_calculate event as ypu
said?
Please show me how the code has to be addapted to both these extra wishes
(sorry that I dit not state these aspects the first time).
Jack.
I changed the decimal case as follows (
'decimal
Target.NumberFormat = "########0.??"
one ? less for only two decimals)
It appears that
30,004 results in 30, should be 30
30,4 results in 30,4 should be 30,40
If indeed the values were the results of formulas, should
If Target.HasFormula Then Exit Sub
be changed in a lead to another (new) part of this sub that is adapted to
work with the results of formulae and using worksheet_calculate event as ypu
said?
Please show me how the code has to be addapted to both these extra wishes
(sorry that I dit not state these aspects the first time).
Jack.
D
Dave Peterson
It sounds like you want to show the integer value if it would be an integer if
you rounded the value to 2 decimal places. If that's true, then try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue As Double
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
myValue = Target.Value
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
Target.NumberFormat = "######0_._0_0"
Else
'decimal
Target.NumberFormat = "######0.??"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
And you'll have to tell the worksheet_calculate event what cells to look at.
(I'm assuming it's not the whole usedrange.)
Private Sub Worksheet_Calculate()
Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range
Set myRng = Me.Range("c1:c99") 'or whatever you want.
For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.??"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell
End Sub
you rounded the value to 2 decimal places. If that's true, then try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue As Double
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
myValue = Target.Value
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
Target.NumberFormat = "######0_._0_0"
Else
'decimal
Target.NumberFormat = "######0.??"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
And you'll have to tell the worksheet_calculate event what cells to look at.
(I'm assuming it's not the whole usedrange.)
Private Sub Worksheet_Calculate()
Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range
Set myRng = Me.Range("c1:c99") 'or whatever you want.
For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.??"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell
End Sub
Jack said:Dave,
I changed the decimal case as follows (
'decimal
Target.NumberFormat = "########0.??"
one ? less for only two decimals)
It appears that
30,004 results in 30, should be 30
30,4 results in 30,4 should be 30,40
If indeed the values were the results of formulas, should
If Target.HasFormula Then Exit Sub
be changed in a lead to another (new) part of this sub that is adapted to
work with the results of formulae and using worksheet_calculate event as ypu
said?
Please show me how the code has to be addapted to both these extra wishes
(sorry that I dit not state these aspects the first time).
Jack.
J
Jack Sons
Dave,
Very kind that you helped again, I think I'm allmost there.
Two remaining questions:
First.
In both subs, when the result is for instance 30,4 it should be 30,40.
Because more precisely stated what I want is:
"If the number after rounding to two decimals is integer, it should show no
decimal point and no decimals (two zeros) right of the (not showing) decimal
point.
If the number after rounding to two decimals is not integer it should always
show two decimals, even if the rightmost decimal (second decimal) is zero.
All results vertically aligned at the decimal point, wether it shows or not"
Second.
You said:
Your second sub "Private Sub Worksheet_Calculate()" works through a for next
loop - which can take a lot of time for a large spreadsheet with a lot of
formulas, as is the case in my workbook - and the loop is triggered by the
calculate event. Can it be done fore all formula cells in the sheet in the
the same time, perhaps by something like
for each c in my....
or perhaps with something like
SpecialCells(xlCellTypeFormulas)
I would be very grateful if you clarify these last two points (missing zero
and all in one execution).
Jack.
Very kind that you helped again, I think I'm allmost there.
Two remaining questions:
First.
In both subs, when the result is for instance 30,4 it should be 30,40.
Because more precisely stated what I want is:
"If the number after rounding to two decimals is integer, it should show no
decimal point and no decimals (two zeros) right of the (not showing) decimal
point.
If the number after rounding to two decimals is not integer it should always
show two decimals, even if the rightmost decimal (second decimal) is zero.
All results vertically aligned at the decimal point, wether it shows or not"
Second.
You said:
Your second sub "Private Sub Worksheet_Calculate()" works through a for next
loop - which can take a lot of time for a large spreadsheet with a lot of
formulas, as is the case in my workbook - and the loop is triggered by the
calculate event. Can it be done fore all formula cells in the sheet in the
the same time, perhaps by something like
for each c in my....
or perhaps with something like
SpecialCells(xlCellTypeFormulas)
I would be very grateful if you clarify these last two points (missing zero
and all in one execution).
Jack.
D
Dave Peterson
the easy change:
Change:
Target.NumberFormat = "######0.??"
to:
Target.NumberFormat = "######0.00"
The .?? says reserve room for up to two decimals, but it won't always show them.
The more difficult one.
I think you're going to have to live with the pain of waiting for excel to loop
through the cells--but the number of cells could be reduced using specialcells
and even more looking for just numbers within that.
Private Sub Worksheet_Calculate()
Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Range("c1:c99") _
.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
If myRng Is Nothing Then Exit Sub
For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.00"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell
End Sub
=========
I'm not sure if this is an alternative for you, but maybe you could do all your
data entry and then run a macro (tools|macro|macros...) that does the
formatting.
It wouldn't be "realtime" and you'd have to remember to run it when you were
finished (well, each time you thought you were finished).
But it would be a one time shot.
Change:
Target.NumberFormat = "######0.??"
to:
Target.NumberFormat = "######0.00"
The .?? says reserve room for up to two decimals, but it won't always show them.
The more difficult one.
I think you're going to have to live with the pain of waiting for excel to loop
through the cells--but the number of cells could be reduced using specialcells
and even more looking for just numbers within that.
Private Sub Worksheet_Calculate()
Dim myValue As Variant
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Range("c1:c99") _
.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
If myRng Is Nothing Then Exit Sub
For Each myCell In myRng.Cells
myValue = myCell.Value
If Application.IsNumber(myValue) Then
If CLng(myValue) = myValue _
Or CLng(myValue) = Round(myValue, 2) Then
'whole number or rounds to a whole number
myCell.NumberFormat = "######0_._0_0"
Else
'decimal
myCell.NumberFormat = "######0.00"
End If
Else
myCell.NumberFormat = "General"
End If
Next myCell
End Sub
=========
I'm not sure if this is an alternative for you, but maybe you could do all your
data entry and then run a macro (tools|macro|macros...) that does the
formatting.
It wouldn't be "realtime" and you'd have to remember to run it when you were
finished (well, each time you thought you were finished).
But it would be a one time shot.
Jack said:Dave,
Very kind that you helped again, I think I'm allmost there.
Two remaining questions:
First.
In both subs, when the result is for instance 30,4 it should be 30,40.
Because more precisely stated what I want is:
"If the number after rounding to two decimals is integer, it should show no
decimal point and no decimals (two zeros) right of the (not showing) decimal
point.
If the number after rounding to two decimals is not integer it should always
show two decimals, even if the rightmost decimal (second decimal) is zero.
All results vertically aligned at the decimal point, wether it shows or not"
Second.
You said:
Your second sub "Private Sub Worksheet_Calculate()" works through a for next
loop - which can take a lot of time for a large spreadsheet with a lot of
formulas, as is the case in my workbook - and the loop is triggered by the
calculate event. Can it be done fore all formula cells in the sheet in the
the same time, perhaps by something like
for each c in my....
or perhaps with something like
SpecialCells(xlCellTypeFormulas)
I would be very grateful if you clarify these last two points (missing zero
and all in one execution).
Jack.
J
Jack Sons
Dave,
This will do. I can of course limit the execution to only those colums for
which this matter is of importance.
Will try out your latest code at a later moment, it is 02.40 now, will get
some sleep.
Again many thanks, I learned a lot.
Jack.
This will do. I can of course limit the execution to only those colums for
which this matter is of importance.
Will try out your latest code at a later moment, it is 02.40 now, will get
some sleep.
Again many thanks, I learned a lot.
Jack.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.