Changing number format to ten thousands

[kirkley08]

Hi, I have loads of data in numbers and I want to change it to the format of ten thousands.
For example:
20,000 = 2.00
200,000 = 20.00
65,887 = 6.59
894,021 = 89.40

I've been searching for the answer but only for thousands and milions.

 

[kirkley08]

Why not just divide the value in column A by 10000 and format as a number with two decimal places, i.e.
formula to put in cell B2:

=A2/10000

I'm thinking of using format instead because I want it to be changed automatically whenever I put a number in the cell.

 

[Joe4]

I'm thinking of using format instead because I want it to be changed automatically whenever I put a number in the cell.

I would then recommend to use VBA to automatically divide it by 10000 upon entering.

If you want to do that, right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the VBA Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
'   See if value entered in is in column A
    If Target.Column = 1 Then
'       See if value entered is numeric
        If IsNumeric(Target) Then
'           Divide by 10000
            Application.EnableEvents = False
            Target.Value = Target.Value / 10000
            Target.NumberFormat = "0.00"
            Application.EnableEvents = True
        End If
    End If
    
End Sub

This is set to work on entries in column A, but can easily be changed if we need it to run on a different column.

 

[awoohaw]

Why not just divide the value in column A by 10000 and format as a number with two decimal places, i.e.
formula to put in cell B2:

=A2/10000

@Joe4 , that is good if that cell is the final destination for the value. But if the OP is going to use it further downstream it changes the value. Of course can always multiply it by 10000 in the next downstream formula.

 

[Joe4]

@Joe4 , that is good if that cell is the final destination for the value. But if the OP is going to use it further downstream it changes the value. Of course can always multiply it by 10000 in the next downstream formula.

Yep, that is exactly what I would do, if needed in further calculations (multiply by 10000).

 

[kirkley08]

I would then recommend to use VBA to automatically divide it by 10000 upon entering.

If you want to do that, right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the VBA Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
'   See if value entered in is in column A
    If Target.Column = 1 Then
'       See if value entered is numeric
        If IsNumeric(Target) Then
'           Divide by 10000
            Application.EnableEvents = False
            Target.Value = Target.Value / 10000
            Target.NumberFormat = "0.00"
            Application.EnableEvents = True
        End If
    End If
   
End Sub

This is set to work on entries in column A, but can easily be changed if we need it to run on a different column.

Well I think this is the most easy way to set the format for the whole column for now, although yeah there are many different ways too such as dividing the cells value by 10000.

Thank you again for all of you good sirs! I appreciate the help!

 

[Joe4]

You are welcome.
Glad we were able to help!