Add Decimal to Existing Number?

[ktm959]

Hi Everyone --

I have what is probably an easy question. I have a column of data which consists of 4 numeric values such as 0040. I need to add a decimal all the way down the column to have a resulting value of 004.0. I have about 150,000 rows...

Thanks in advance for any help!

 

[Jaye7]

If they are all stored as text and are all 4 digits long you could paste this formula into the column next to your column and change the A1 to match your column, then double click the cell marker to copy down your page.

=LEFT(A1,3)&"."&RIGHT(A1,1)

 

[Trouttrap2]

hello
If you want to maintain the integrity of the number instead of using text, an alternative is to use the custom format feature on your range of cells. Highlight your cells, then Click Ctrl+F1 >Number>Custom> enter 000.0 into the Custom field and hit enter. That will format your number as you want but keep it as a number.

 

[ktm959]

Thank you so much! It is working correctly, took a while to do auto-fill. I'm not sure yet about preserving the number, I don't think it is necessary for my application -- I am importing these numbers as product weights into an e-commerce platform so I'm pretty sure it will work as text.

Thank you guys again, much appreciated!

 

[mikerickson]

Enter .1 in a spare cell
Copy that cell
Select the range to be changed.
PasteSpectial>Mathematics>multiply