4

I have an XLSX file which I am opening with Excel 2016.

This file includes a column "Code" which contains barcode values. The column has a number format of General.

Looking at this column, in the cell itself, a values will appear as 6.16611E+12; scientific notation.

In the formula bar (if I click on the cell), that same value will appear as 6161101860079.

I want to preserve the data in the "long format" (e.g. the 6161101860079 format, not the scientific notation values) when converting this XLSX to a CSV.

Currently, if I use “Save As” to save the file as a CSV, the long format values are lost. When I open the CSV with notepad, I can see that it only contains "Code" values in the 6.16611E+12 format.

How can I ensure that the values in the code column are kept as these long number values?

I have tried to format the XLSX Code column as Text before saving as CSV, but that does not change the outcome.

Improve this question

2 Answers 2

Reset to default
5

Change the number format in the cell to Number with 0 decimal places and "Use 1000 Separator" unchecked, and then save. A csv file basically saves what it "sees" in the cells.

Improve this answer
1
  • This worked perfectly, thank you for your help.
    – Jay Bee
    Commented Jan 17 at 22:07
4

Excel saves data in csv as it is shown in cells, as @MGonet explained.

LibreOffice Calc, on the other hand, has the explicit checkbox in the Save as CSV dialog ("Save cell content as shown"), so it can behave like Excel, or save actual cell value in a CSV file.

enter image description here

If this checkbox is left unchecked, CSV should containg 6161101860079, regardless of number format.

Improve this answer
1
  • 1
    Sometimes using other tools is the simplest solution.
    – Guntram Blohm
    Commented Jan 18 at 7:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .