How do I change a whole number into a decimal number in Excel?
Example:
I want to make 12435 into 1.2435 and have to do this for 100 lines. The decimal point always goes after the first digit, and I want to replace the original number.
Is there any shortcut for this in Excel?
34 Answers
You can use this formula to shift all the numbers:=A1/10^ROUNDDOWN(LOG10(A1),0)
(note: it works only with numbers >0)
If you prefer not to have a new column and you have all the numbers having the same number of digits (e.g. between 10'000 and 99'999) then just type the divisor (10000 in this example) in a cell, press CTRL+C on this cell then select all your numbers and go to Home - Paste - Paste Special - Divide.
1You don't indicate whether all of the cells have the same number of digits. If not, I suspect you would need an additional column, at least temporarily.
In addition to the mathematical approaches, you could do this with string functions. For example:
=VALUE(LEFT(A1,1)&"."&RIGHT(A1,LEN(A1)-1))This just takes the leftmost digit, appends a decimal point after it, appends the rest of the number, and then turns the string back into a number. Copy the formula down the rows.
If you need to replace the original values, use copy, paste special values to replace the original numbers, after which you can delete the extra column.
Orrr... type 100 in a cell or 1000 or whatever factor you would like to divide by - copy it and then use paste special>Divide on all the relevant cells.
so for example: you have a data set that always consists of 4 numbers, in my case we always tally pipe in the field and its almost always XX.XX long (40'-50'per joint). So I need my excel to put the point in for me. To do this I go to FILE-OPTIONS-ADVANCED- check the box that says "AUTOMATICALLY INSERT A DECIMAL POINT"- set how many places you want it to placed at. In my case I choose 2 places. Voila no formulas, no questions asked, easy peasy.
1
