Excel decimal format
 

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

mlou wrote:
from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

Just highlight the column you want it to affect, then right click and
select format cells.

Bill

what I really need is to be able to type in 12345 and have it show as 123.45
(without typing the decimal)in just one column, not the whole worksheet. by
right-clicking on the column and formatting cells, I still have to type
decimal, don't I?
"Bill Martin -- (Remove NOSPAM from addre" wrote:

mlou wrote:
from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

Just highlight the column you want it to affect, then right click and
select format cells.

Bill

mlou

For just one column you would need event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
'remove the $ sign if don't want currency format
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.


Gord Dibben Excel MVP

On Tue, 19 Apr 2005 14:40:02 -0700, "mlou"
wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

mlou wrote:
what I really need is to be able to type in 12345 and have it show as 123.45
(without typing the decimal)in just one column, not the whole worksheet. by
right-clicking on the column and formatting cells, I still have to type
decimal, don't I?


Having a cell display a different number than you've entered into the
cell is beyond anything I can think of. If it were me, I'd type 12345
into one column and have it display in another one. Divide by 100 and
have the other column formatted to display two decimal positions.

Good luck with it...

Bill

You could create the custom number format 0\.00
That would easily display 12345 as 123.45, but then if you need to reference
the value in a formula you will have to divide it by 100 in the formula.

"mlou" wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

brett,
lots easier than writing script - I'll try it. thanks
mlou

"Brett" wrote:

You could create the custom number format 0\.00
That would easily display 12345 as 123.45, but then if you need to reference
the value in a formula you will have to divide it by 100 in the formula.

"mlou" wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

gord
thanks - sounds complicated (!) but I'll give it a shot.
mlou

"Gord Dibben" wrote:

mlou

For just one column you would need event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
'remove the $ sign if don't want currency format
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.


Gord Dibben Excel MVP

On Tue, 19 Apr 2005 14:40:02 -0700, "mlou"
wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

Try it....you'll like it.

Gord

On Wed, 20 Apr 2005 08:30:03 -0700, "mlou"
wrote:

gord
thanks - sounds complicated (!) but I'll give it a shot.
mlou

"Gord Dibben" wrote:

mlou

For just one column you would need event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
'remove the $ sign if don't want currency format
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.


Gord Dibben Excel MVP

On Tue, 19 Apr 2005 14:40:02 -0700, "mlou"
wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

Excel decimal format
 

This custom number format really helped me. Now, is there a way to add the
comma dividing the thousands? #,###.##

"Brett" wrote:

You could create the custom number format 0\.00
That would easily display 12345 as 123.45, but then if you need to reference
the value in a formula you will have to divide it by 100 in the formula.

"mlou" wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?

Excel decimal format
 

Assuming you will never have a formatted amount of one million dollars or
more, try this Custom Format...

[<100000]0\.00;0\,000\.00

--
Rick (MVP - Excel)


"Sheba" wrote in message
...
This custom number format really helped me. Now, is there a way to add
the
comma dividing the thousands? #,###.##

"Brett" wrote:

You could create the custom number format 0\.00
That would easily display 12345 as 123.45, but then if you need to
reference
the value in a formula you will have to divide it by 100 in the formula.

"mlou" wrote:

from the tools menu, i clicked options, edit and "fixed decimal" to
two.
However, I don't want this to affect the whole worksheet, just one
column.
is there a way to do that?