Excel Decimal Question

[MethadoneBoy]

Hi guys,

I have an column of numerical data in Excel that I want to be able to format in such a way that there will always be 9 characters after a decimal point.

To give you an example :

Say I have two numbers that I wish to input into different fields in this coulmn - 350 and 1,100,000,000.

When I type 350 into the field, I want this to happen :

0.000000350 (9 digits after the decimal)

When I type 1,100,000,000 into the field, I want this to happen :

1.100000000 (9 digits after the decimal)

As you can see from both examples, there always needs to be 9 digits after the decimal point as opposed to just formatting the cells so that a decimal point and 9 zeroes are added after whatever number is input.

Thanks in advance!

[DKenny]

You could use the _Change event of the worksheet, trapping for changes to that column, and then divide the value.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With Target
        'in this example I am checking for
        'values in column "D"
        'You should ammend to reflect your column
        If .Column = 4 Then
            
            'Only run the code on numeric values
            If IsNumeric(.Value) Then
                
                Application.EnableEvents = False
                
                'Divide the number
                .Value = .Value / 1000000000
                
                'Rest the number format (in case of pasted values)
                .NumberFormat = "#,##0.000000000"
                
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub