Moving all decimals after the second number

[jrabi]

I have the following spreadsheet:


Sales

5444.33
12222.55
123.78
7899999.22
65898.56
565.33

I want the values to be:


54.4433
12.22255
12.378
78.9999922
65.89856
56.533


Is there an easy way, I have over 3000 rows..........

 

[Andrew Poulsom]

Try this formula:

=A1/(10^(LEN(INT(A1))-2))

 

[Rick Rothstein]

It looks like you want to end up with a column of numbers having 2 digits in front of the decimal point. I wish you included an example like 1.23 so we would know for sure... the formula below returna 12.3 for that value.

=--REPLACE(SUBSTITUTE(A2,".",""),3,0,".")

 

[jrabi]

Perfection. Thanks.

 

[Rick Rothstein]

Try this formula:

=A1/(10^(LEN(INT(A1))-2))

Nice formula!

My preference would be to convert it to a multiplication instead of a division though (saves 2 characters to boot as well)...

=A1*10^(2-LEN(INT(A1)))

 

[Scott Huish]

Another way:

=A1/10^(INT(LOG(A1)-1))

Although, what if the number is less than 10?

 

[Rick Rothstein]

My preference would be to convert it to a multiplication instead of a division though (saves 2 characters to boot as well)...

=A1*10^(2-LEN(INT(A1)))

DON'T DO THIS

Changing it to a multiplication format makes it return an incorrect result for 7899999.22 (it returns 79, not 78.9999922).