Format Changes

[jackubu]

HI,

The problem i am having is have a list of products for my website and when i change the format of the spreadsheet from a Macro Enabled Workbook to a CSV file my data changes.

The data in the cells is plain data which has been copied and pasted as value so no formulas are attached to it and all of the cells are formatted as text.
When i save the file to a CSV i get the following data changes

Barcodes which look like 5045874123658751 are becoming 5045E+12
and
I have lists of years 2001,2002,2003,2004,2005 and so on which change to 200+21


Does anybody know why this happens and how i can get around it?

[AndyLitch]

Your macro enabled workbook will have cell formatting to display your barcodes as text or literal ... This is purely for worksheet display purposes.... The CSV file is displaying in standard form XX * 10eX but retains the full value of the number the same as the worksheet. If you want to force the CSV file to display the literal (full) value you have a few options :-
1. Precede the barcode numbers with an apostrophe - to force literal display
2. Precede the barcodes with a letter - to force it to display as text

Are your years stored in individual cells because they should store the same in both XLSM and CSV..

[amit.wilson]

Hello J,

For the why question:

Here is some information from an MS support website (http://support.microsoft.com/kb/158071)

"Microsoft Excel supports a maximum of 15 significant digits at all times. This limit applies to a value that is calculated by a formula. Because of this limitation, if at any time a formula calculates a value that exceeds 15 digits in length, digits beyond the fifteenth significant digit are changed to zeroes."

For the how to get around it, format the cell as text and not as a number.

I've worked with the same problem when my company did some work for very long barcodes. Let me know if you need more help.

Cheers

[jackubu]

Hi Andy,
when the CSV file is uploaded to our website as the product database the barcodes remain as the 5045+12 meaning they effectively have no barcode on the website.
The years are all in one cell seperated by commas.

Hi Amit,
I have formatted the cells as text before i saved the cells as a CSV file but when i reopen the file in the new formate the barcodes have changed.

Thanks Both

[amit.wilson]

J - does the problem arise when the CSV file is opened as excel?

[jackubu]

Yes it does amit.

[amit.wilson]

J - you have two options:

1. Use a text editor to open the CSV file. That way the format does not matter

2. Or convert the CSV to excel and format the data as text.

Going from CSV to excel and then saving it again as CSV will be a problem.

Cheers