Whole number as decimal

bohica48

New Member
Joined
Jul 23, 2008
Messages
4
I would like to enter whole numbers but have them convert to decimal. I have searched and found a solution, but it only references to one column and I need to reference other columns as well. I tried to edit but I’m not very knowledgeable with code. Here is an example of what I am looking for, columns E31:E52, F31:F52, L31:L52, M31:M52, N31:N52. Could someone provide a code to acquire these results? Thanks in advance.
<o:p> </o:p>
Excel version 2002
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So you want a number like "102954" to convert to "0.102954"?

If so, =VALUE("."&A1) Assuming your data begins in A1.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Welcome to the board.

Just format (format --> cells --> number) the desired cells as numbers with however many decimal places.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994

ADVERTISEMENT

If you're after Keith's suggestion, you can use tools --> options --> edit to set "fixed decimal places" to 6. Then, entering 102954 will store the value as 0.102954 in the cell.

Just turn "fixed decimal places" off when you're done, as the setting applies to every sheet on all open workbooks.
 

bohica48

New Member
Joined
Jul 23, 2008
Messages
4
Sorry for not being clear on the outcome. I need this for specific columns as others require actual whole numbers.

Example:
enter value of 45
Outcome as 0.45

The reason I want to be able to do this is not having to put a decimal for each entry, in the end I average each column. Hope this helps.

Thanks again,
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I believe that any suggestion above will work for you. With mine, you have to make an extra column and use the formula. Then if you want to get rid of the column, you have to copy > paste special (values) over what you entered.
 

bohica48

New Member
Joined
Jul 23, 2008
Messages
4
I used the code below which I found here, it worked for a particular defined column but I could not get it to work with multiple columns as I mentioned in my original post.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)</pre>
<o:p> </o:p></pre>
Application.EnableEvents = False</pre>
</pre>
Dim isect As Range</pre>
Set isect = Intersect(Target, Range("E1:E65536"))</pre>
If Not (isect Is Nothing) And IsNumeric(Target.Value) Then</pre>
Target = "0." & Target</pre>
End If</pre>
</pre>
Application.EnableEvents = True</pre>
</pre>
End Sub
</pre>
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
Change this line to
Code:
Set isect = Intersect(Target, Range("E31:E52, F31:F52, L31:L52, M31:M52, N31:N52"))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,221
Messages
5,594,898
Members
413,950
Latest member
solve22

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
Top