Replace Numbers 1

[cmonthetic]

I have an Excel 97 sheet that contains several thousands of numbers that should all be to 1 decimal place (e.g. 2.4)

The issue I have is that some of the data has been copied in from other sheets and it does not follow the decimal place rule, you can change the format and it looks correct but it is the actual real number that I need to change.

Is there a quick method of changing actual numbers from x.xxxxxxxxxx to just x.x

TIA

 

[DPlank]

You'd need to look at the ROUND() function for this.

For example, =ROUND(A1, 1) will give you 2.4 where the number is 2.36784122635152631245241521584351

Thanks
Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[TonyJollans]

Hi ,

After formatting your numbers, go to Tools > Options > Calculation tab, and under Workbook options, check "Precision as Displayed". You'll get a warning that you're about to permanently change data - click OK and you're done.

New data typed in the cells will be automatically rounded to 1dp (the actual data not just the display). Cells copied in will have the format of the original cells - if that's not Number with 1dp then you'll have to reformat the cells but if it is, then the data will be truncated as part of the paste operation.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[