4

This question already has an answer here:

I have a question regarding Excel. We generate automatically a CSV file everyday, containing stuff looking like :

column1;column2;...;columnx
text;date;text;249316479917397279

The issue is that the number 249316479917397279 is too long now to be displayed by Excel entirely. Excels shows "2,49316E+17" and even when I go edit the cell I see 249316479917397000.

The number is rounded by Excel and I lose the three last numbers, which are important for me since these numbers are IDs. I would Excel to display it as a text, without no rounding. I tried to generate my csv with quotes "249316479917397279" but dosn't change anything.

Is it possible?

marked as duplicate by CharlieRB, fixer1234, Karan, DavidPostill, MariusMatutiae Jun 13 '15 at 12:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • According to this answer, formatting the column in question as text before importing the file should probably fix your issue? – eirikdaude Jun 9 '15 at 8:53
2

When you open a .csv Excel skips the "text import wizard" if you change the file to be .txt then open it the dialogue will come up and you can specify that the affected columns are treated as text.

Note if you treat columns as 'Text' not 'General' in some (all?) versions of Excel the data contained is truncated to 256 characters.

  • Which begs the question: How do you convert those text columns back to numbers? – zylstra Jan 30 '18 at 18:12
  • You must specify each column separately. What a pain. – zylstra Jan 30 '18 at 18:13
  • You can select across ranges using a shift select (as with cells by selecting one then holding down shift) – Nate Sep 28 '18 at 9:39
-1

I would avoid to open the file in a text editor and paste it in excel with a good formated column everytime.

Something working is to add ' before my number when I generate the CSV. I see the ' in excel but well, better than nothing.

Not the answer you're looking for? Browse other questions tagged or ask your own question.