0

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?

Improve this question
3
  • Do you want the decimal number to replace what was in the cell?
    – eirikdaude
    Jun 12, 2015 at 9:51
  • 1
    This question is much too vague to answer: one example isn't enough to say what you want. Are you trying to divide every number by 10000? Are you trying to rewrite every number so the decimal point comes after the first digit? Something else?
    – David Richerby
    Jun 12, 2015 at 10:04
  • Yes ... i want to rewrite the every numbers in the particular column. i want decimal point after first digit.
    – user458062
    Jun 12, 2015 at 10:44

4 Answers 4

Reset to default
3

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.

Improve this answer
1
  • Thank you so much for your quick reply.. its very useful for me..
    – user458062
    Jun 12, 2015 at 12:00
3

You 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.

Improve this answer
1
  • I prefer MID(A1,2,LEN(A1) instead of RIGHT(A1,LEN(A1)-1) for no particular reason than it being more intuitive to me. +1 for the obvious and simplest answer.
    – Engineer Toast
    Jun 12, 2015 at 13:43
0

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.

Improve this answer
-1

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.

Improve this answer
1
  • 1
    But the question doesn't ask about getting a certain number of decimal places, it asks how a add a decimal point after the first digit (so there could be any number of decimal places after it).
    – Blackwood
    May 8, 2018 at 16:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .