Convert numbers into decimals

[44444]

Hello, I would like to convert numbers typed into a decimal with 8 places. For example 123 into .00000123. I know there is an option in advanced settings "Automatically insert a decimal point" but I only want this to be done in certain columns.

Thank you!

 

[Rick Rothstein]

Hello, I would like to convert numbers typed into a decimal with 8 places. For example 123 into .00000123. I know there is an option in advanced settings "Automatically insert a decimal point" but I only want this to be done in certain columns.

What columns would they be? What I am interested in knowing is whether the columns are contiguous or non-contiguous.

 

[44444]

What columns would they be? What I am interested in knowing is whether the columns are contiguous or non-contiguous.

C and D

 

[Rick Rothstein]

Give this VBA event code a try...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Columns("C:D")) Is Nothing Then
    For Each Cell In Intersect(Target, Columns("C:D"))
      If Cell <> "" And Not Cell.Value Like "*[!0-9]*" Then Cell.Value = Cell.Value / 100000000
    Next
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[44444]

Give this VBA event code a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Columns("C:D")) Is Nothing Then
    For Each Cell In Intersect(Target, Columns("C:D"))
      If Cell <> "" And Not Cell.Value Like "*[!0-9]*" Then Cell.Value = Cell.Value / 100000000
    Next
  End If
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Perfect, thank you!