Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 19th 10, 03:02 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 1
Default eliminating decimal places

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?

  #2   Report Post  
Old March 19th 10, 03:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default eliminating decimal places

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

On Fri, 19 Mar 2010 08:02:01 -0700, Catherine wrote:

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?


  #3   Report Post  
Old March 21st 10, 09:34 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 2
Default eliminating decimal places

On Mar 19, 3:02*pm, Catherine wrote:
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(...,...).
  #4   Report Post  
Old March 21st 10, 09:37 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 2
Default eliminating decimal places

On Mar 21, 9:34*am, "
wrote:
On Mar 19, 3:02*pm, Catherine wrote:

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(...,...).


Sorry - line 8 SHOULD SAY "try using =round(range,2)".
  #5   Report Post  
Old October 10th 14, 11:29 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2014
Posts: 1
Default eliminating decimal places

On Friday, March 19, 2010 3:02:01 PM UTC, Catherine wrote:
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'


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Decimal Places JHL Excel Discussion (Misc queries) 4 April 24th 07 06:44 PM
Subtracting two 2-decimal place numbers gives result 13-decimal places? [email protected] Excel Worksheet Functions 5 March 12th 07 10:38 PM
Max decimal places SusieQ Excel Discussion (Misc queries) 1 July 13th 05 07:57 PM
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. SUKYKITTY Excel Discussion (Misc queries) 3 July 6th 05 01:50 PM
add 2 decimal places Bill M. Excel Discussion (Misc queries) 2 January 28th 05 10:21 PM


All times are GMT +1. The time now is 10:41 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017