Keep decimals when converting to csv

Topic

New Reply

I have an Excel file with a column of whole numbers. I need them to have 2 decimals like 275.00, no dollar sign. I formatted the column with currency no $. But when I convert the file to a csv, it goes back to 275. Is there a way i can keep the 275.00? Thanks

Reply | Quote

Replies

Is it the SAVE As that is the issue or reopening the file?

I am unable to reproduce the Save As problem. When I save as a CSV, the file saves the text the way that it is displayed.

I do lose it when the CSV is re-opened in XL, but this expected as it is an XL formatting issue where it is displayed as general unless there is some indication in the number that it should be a special format.

Steve

Reply | Quote

Forget this reply – see the next one by Steve.

The number format merely tells Excel how to display the value. When you export to csv, the internally stored value 275 is exported, not the displayed version.
If you want 275.00 in the exported file, you have two options:

1. Store the values as text, either by formatting the column as text and entering 275.00, or by prefixing the value with an apostrophe: ‘275.00.

– or –

2. Keep the values as numbers, and create a column with formulas next to it, like this:

=TEXT(A1,”0.00″)

or

=TEXT(A1,”#,##0.00″)

if you want thousands separators.

Reply | Quote

The number format merely tells Excel how to display the value. When you export to csv, the internally stored value 275 is exported, not the displayed version.

Is this different in different versions? When I checked in XL2002 what was put into the CSV was the formatted text (with the decimal points and even with the dollar sign in currency mode). When re-imported excel removed those decimal digits just like if one had entered them manually (except excel dutifully formatted the text to currency with 2 decimals for the importing of a number that was clearly currency)…

Steve

Reply | Quote

I’m getting rusty – I should start checking my replies! You’re completely correct – the .csv file contains formatted values but Excel ignores the formatting when opening the .csv file.

Reply | Quote