eliminating decimal places

C

Catherine

On an excel spreadsheet downloaded from an external source there are eight
decimal places in the numbers. If I format to two decimals it displays the
number with two decimals but still keeps the eight decimals when in the cell.
This makes it difficult to upload the info into our ERP system as that system
rounds differently. How can I permanently get rid of the extra decimals?
 
G

Gord Dibben

Set Calculation options to "precision as displayed"

Not this a workbook setting and will affect all numbers.

There is no returning after numbers have changed.


Gord Dibben MS Excel MVP
 
J

johnfones008

On an excel spreadsheet downloaded from an external source there are eight
decimal places in the numbers. If I format to two decimals it displays the
number with two decimals but still keeps the eight decimals when in the cell.
This makes it difficult to upload the info into our ERP system as that system
rounds differently. How can I permanently get rid of the extra decimals?

If you don't wish to change the whole workbook, then perhaps you could
try using =round([range],2).
If you wish to convert just one sheet, then use this function in all
of the equivalent cells in a new sheet, i.e. try making NewSheet!A1
=round(OldSheet!A1,2) and so on.

If your rounding system requires a different direction, then perhaps
try =roundup(...,...) or =rounddown(...,...).
 
J

johnfones008

On an excel spreadsheet downloaded from an external source there are eight
decimal places in the numbers. If I format to two decimals it displays the
number with two decimals but still keeps the eight decimals when in thecell.
This makes it difficult to upload the info into our ERP system as that system
rounds differently. How can I permanently get rid of the extra decimals?

If you don't wish to change the whole workbook, then perhaps you could
try using =round([range],2).
If you wish to convert just one sheet, then use this function in all
of the equivalent cells in a new sheet, i.e. try making NewSheet!A1
=round(OldSheet!A1,2) and so on.

If your rounding system requires a different direction, then perhaps
try =roundup(...,...) or =rounddown(...,...).

Sorry - line 8 SHOULD SAY "try using =round(range,2)".
 
A

akemballcook

On an excel spreadsheet downloaded from an external source there are eight
decimal places in the numbers. If I format to two decimals it displays the
number with two decimals but still keeps the eight decimals when in the cell.
This makes it difficult to upload the info into our ERP system as that system
rounds differently. How can I permanently get rid of the extra decimals?


What i did was
- us the round function get the required rounding.
- copy the new figures
- paste new figures as 'values'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top