Question Locked

Excel 2007 - converting existing numbers to numbers with decimals

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello All first time on here.

I'm currently working with in a Microsoft Excel 2007 worksheet that has 570K+ rows.

One of the columns has numbers -0000027, -0000048, -0000102 etc

My problems is that I received this file in .txt format, converted it to .xlsx. The numbers displayed above are actually incorrect, they should be decimal places eg. -0000027 should be -0.27.

I have tried using the Excel Option, Advanced then clicking the box for 2 decimal places.

Problem is the only way to update the cells to reflect the change is when I individually click on the cell and then hit enter. Given I have 570K+ rows is there an easier way.

I would really appreciate any help you may be able to provide.

2 replies

by thumbsup2

First, it sounds as if you imported that column as text instead of a number, since it has retained the leading zeros. The only way I can force Excel to retain the zeros is to type it in as '-0000027 which left aligns it indicating that it's text (non-numeric)

No matter though, you can still convert all of the cells.

Start in the top row of data and use a cell well to the right of the existing data, out of the way. You will delete this entire column later.

Type the following formula into the first row, directly across from where your data resides and press Enter. Then, place the cursor back into that cell, grab the little box in lower right corner and drag it clear to the bottom of the data (copy down). It will change the cell references as it copies down each row. Leave the highlighting in place though.

Now that you have the values displayed in that new column and you are certain this is what you want, copy them all (CTRL+C or right click and copy). They should all still be highlighted from the previous step.

Now, place the cursor in the cell at the top of the row of data in the column where the incorrectly displayed numbers reside. Right click and choose Paste SPECIAL. From the menu that pops up, choose VALUES. This will replace the entire column with numbers that have the leading zero and two decimal places.

Now, here's the formula. It will check the contents of the cell containing your data to make sure it's not empty, then multiply it by .01 to place the decimal where you want it. Replace the cell reference in the formula (A2) to what ever the top cell of your currently incorrect data really is.

=IF(A2<>"",A2*0.01,"")

After you've got your numbers pasted back in, it's safe to delete the new column you created.

This discussion is closed to new replies.