Auto-Convert Decimal Numbers to Specific Cells

[Disarmonious]

Hello,

I’m trying to figure out how to customize specific cells to add 2 decimal places when typing a number. For instance, I would like all numbers within cell range B2:B20 to add two decimal points when typing a number. E.g. – In cell B2, if I enter 1234, I want that cell to display 12.34. In cell B3, I enter 23873, I would like that cell to convert this number to 238.73, etc. Keep in mind that I only want dedicated cells to automatically convert two decimal places and not the entire worksheet.

 

[Disarmonious]

Hello,

Try custom formatting those cells to "00\.00".

 

[Rick Rothstein]

I’m trying to figure out how to customize specific cells to add 2 decimal places when typing a number. For instance, I would like all numbers within cell range B2:B20 to add two decimal points when typing a number. E.g. – In cell B2, if I enter 1234, I want that cell to display 12.34. In cell B3, I enter 23873, I would like that cell to convert this number to 238.73, etc. Keep in mind that I only want dedicated cells to automatically convert two decimal places and not the entire worksheet.

Do you want the underlying values in the cell to physically change (type 1234 and have the value actually be 12.34) or simply display with two decimal places but still remain what the user typed originally (user types 1234, cell displays 12.34 but Formula Bar still shows 1234)?

 

[Disarmonious]

Hello Rick,

Yeah, I want the values to change from 1234 to 12.34, if 1234 is typed in the specified cell. I think my discovery of custom formatting those cells to "00\.00" worked for me. I'm going to keep testing these cells, and if this doesn't work out for me, then I'll need further assistance on this topic. In the meantime, if there are other ways for me to get these results, your suggestions would be greatly appreciated. Thank you for your time.

 

[JoeMo]

You could use a change event macro like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range
Application.ScreenUpdating = False
Set R = Me.Range("B2:B20")   'set the range to monitor here
If Not Intersect(Target, R) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, R)
        If IsNumeric(c.Value) Then c.Value = c.Value / 100
    Next c
    Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub