Number formating

[fraz627]

I have a numeric cell, that I would like to be able to enter "-123" and the cell displays "-1.23" or if the user enters "-1.23" the cell will display "-1.23"

is this even possible?

Thanks

 

[footoo]

I think only with VBA.

Do you want to do for one particular cell or a range?
Do you want to do only for -123 and -1.23 or for any negative number?
What about positive numbers?

 

[fraz627]

The cells will be in a range by columns, the numbers will be either negative or positive
I also feel that it can only be done via VBA

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Range("A:A") 'Change as required
Set rng = Intersect(rng, Target)
If Not rng Is Nothing Then
    rng.NumberFormat = "#,##0.00;-#,##0.00"
    Application.EnableEvents = False
    For Each cel In rng
        If IsNumeric(cel) Then If cel = Int(cel) Then cel = cel / 100
    Next
    Application.EnableEvents = True
End If
End Sub

 

[fraz627]

Great, is was sort of leaning that way.
great job