Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Preserving "trailing" zeros from csv file

When opeing a csv file in Excel, Excel drops any zero-value decimal points.
E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are there.

I can't find where I can change Excel's settings to prevent this from
happening.

Any ideas?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Preserving "trailing" zeros from csv file

Yes, I can help you with that. Excel has a default setting that automatically removes trailing zeros from numbers when you open a CSV file. However, you can change this setting to preserve the zeros. Here's how:
  1. Open Excel and click on the File tab.
  2. Click on Options.
  3. In the Excel Options dialog box, click on the Advanced tab.
  4. Scroll down to the section labeled "When calculating this workbook" and find the option labeled "Automatically insert a decimal point".
  5. Check the box next to "Automatically insert a decimal point".
  6. Click OK to save the changes.

Now, when you open a CSV file in Excel, it will preserve the trailing zeros. If you have already opened the CSV file and lost the trailing zeros, you can still add them back by formatting the cells as text. Here's how:
  1. Select the cells that contain the numbers you want to format.
  2. Right-click on the selection and choose Format Cells.
  3. In the Format Cells dialog box, click on the Number tab.
  4. In the Category list, choose Text.
  5. Click OK to save the changes.

Now, the cells will display the numbers with the trailing zeros. Keep in mind that formatting the cells as text will prevent you from using them in calculations, so you may want to convert them back to numbers if you need to perform any calculations.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 44
Default Preserving "trailing" zeros from csv file

After opening, you can just format the columns with numbers and dates if you
want. You'll lose that formatting, though, if you save the file as a CSV
again.

"hgarrison" wrote:

When opeing a csv file in Excel, Excel drops any zero-value decimal points.
E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are there.

I can't find where I can change Excel's settings to prevent this from
happening.

Any ideas?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Preserving "trailing" zeros from csv file

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open), and
specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are there.

I can't find where I can change Excel's settings to prevent this from
happening.

Any ideas?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Preserving "trailing" zeros from csv file

Thanks. When I save the file as .csv, close the file, and reopen it, Excel
still drops the trailing zeros. I'm hoping there is some setting or default
that I can change to prevent Excel from doing this.

"David Biddulph" wrote:

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open), and
specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are there.

I can't find where I can change Excel's settings to prevent this from
happening.

Any ideas?






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default Preserving "trailing" zeros from csv file

The only way to preserve zeros after the decimal point is to convert the
number to text before saving the CSV.

Regards,
Fred

"hgarrison" wrote in message
...
Thanks. When I save the file as .csv, close the file, and reopen it,
Excel
still drops the trailing zeros. I'm hoping there is some setting or
default
that I can change to prevent Excel from doing this.

"David Biddulph" wrote:

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open), and
specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are there.

I can't find where I can change Excel's settings to prevent this from
happening.

Any ideas?





  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Preserving "trailing" zeros from csv file

My message told you how to reopen it in Excel without losing the trailing
zeros. [If you open the csv with Notepad you'll see that the trailing zeros
aren't lost in the saving operation, so they are still there in the text in
the csv file.]
No, there isn't a setting you can change. If you save as txt rather than
csv, then you'll always get the opportunity to set the options for each
column rather than Excel making its own decisions.
--
David Biddulph

hgarrison wrote:
Thanks. When I save the file as .csv, close the file, and reopen it,
Excel still drops the trailing zeros. I'm hoping there is some
setting or default that I can change to prevent Excel from doing this.

"David Biddulph" wrote:

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open), and
specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are
there.

I can't find where I can change Excel's settings to prevent this
from happening.

Any ideas?



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Preserving "trailing" zeros from csv file

Which version of Excel behaves that way, Fred?

In Excel 2003, if you have one cell with 123 formatted as a number with 3
decimal places to show 123.000, and another cell with the text value
123.000, when it is saved as a csv they both show as 123.000 in the csv
file. I'm interested to know version you have if it behaves differently.
--
David Biddulph

Fred Smith wrote:
The only way to preserve zeros after the decimal point is to convert
the number to text before saving the CSV.

Regards,
Fred

"hgarrison" wrote in message
...
Thanks. When I save the file as .csv, close the file, and reopen it,
Excel
still drops the trailing zeros. I'm hoping there is some setting or
default
that I can change to prevent Excel from doing this.

"David Biddulph" wrote:

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open),
and specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are
there. I can't find where I can change Excel's settings to prevent this
from happening.

Any ideas?



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default Preserving "trailing" zeros from csv file

My mistake, David. I was probably thinking of Lotus which put quotes around
text values, but Excel certainly doesn't. So, as you say, the formatting is
lost on reopen. Thanks for pointing out the error.

Regards,
Fred.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Which version of Excel behaves that way, Fred?

In Excel 2003, if you have one cell with 123 formatted as a number with 3
decimal places to show 123.000, and another cell with the text value
123.000, when it is saved as a csv they both show as 123.000 in the csv
file. I'm interested to know version you have if it behaves differently.
--
David Biddulph

Fred Smith wrote:
The only way to preserve zeros after the decimal point is to convert
the number to text before saving the CSV.

Regards,
Fred

"hgarrison" wrote in message
...
Thanks. When I save the file as .csv, close the file, and reopen it,
Excel
still drops the trailing zeros. I'm hoping there is some setting or
default
that I can change to prevent Excel from doing this.

"David Biddulph" wrote:

Import the file as text.
Data/ Import External Data (or rename the .csv as .txt and open),
and specify the columns as Text.
--
David Biddulph

hgarrison wrote:
When opeing a csv file in Excel, Excel drops any zero-value decimal
points. E.g. 12500.00 becomes 12500

When I open the file in Word, the zero-value decimal points are
there. I can't find where I can change Excel's settings to prevent
this
from happening.

Any ideas?




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
Converting "uppercase" string data to "lower case" in CSV file [email protected] Excel Discussion (Misc queries) 2 August 12th 08 08:36 PM
text string: "91E10" in csv file auto converts to: "9.10E+11" [email protected] Excel Discussion (Misc queries) 2 August 12th 08 03:13 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F daves Excel Discussion (Misc queries) 3 April 24th 07 04:52 AM
Utility to "clean up" or "defrag" large Excel file Sabrina Excel Discussion (Misc queries) 3 January 12th 06 09:57 PM


All times are GMT +1. The time now is 03:12 AM.

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

About Us

"It's about Microsoft Excel"