Number formating

fraz627

Board Regular
Joined
Apr 26, 2014
Messages
99
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?
 
Upvote 0
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
 
Upvote 0
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top