3

Suppose I have a following string in a .csv file which I am trying to open in Excel 2010:

1073741824;1073741824;1073751824;1073741832;1152921513196781569

However, when I actually open it, the value 1152921513196781569 changes to 1,15292E+18, and even if I change the cell content type to numeric with zero fractional precision, it yields only 1152921513196780000.

Is there any way to overcome this issue?

Brad Patton
  • 10,668
wh1t3cat1k
  • 181

3 Answers3

4

Open a new workbook. Use the Data->Get External Data->From Text tool to import your CSV. In the wizard, specify the formatting for that field as Text.

Excellll
  • 12,847
2

One possibility is to first format the sheet as text and then paste from a text editor. Here are the steps:

  • In a blank sheet select all cells and set the number format to Text
  • Enter something in A1 and then choose Data|Text to Columns|Delimited|Semicolon
  • Open the csv file in notepad select all with Ctrl+A and copy and paste to the sheet
lori_m
  • 486
2

Both the given answers are correct. I offer a third alternative, along with some explanation:

When Excel opens a CSV file, it skips the "text import wizard", performing the data identification for you. It will try to be helpful, and detect numbers, dates, currencies... This causes your 1152921513196781569 to be shown (and saved!) as 1,15292E+18

Apart from copy-pasting the data (which is fast, but annoying when the data size is huge) or using the data-get external data, here is an alternative:

Rename the file to a .txt extension.

Then open the .txt file with Excel. It will show the data import wizard, and you can select all fields and mark them all as text. Now when you save as CSV, the numbers won't get transformed.

Community
  • 1
Konerak
  • 1,915
  • 3
  • 20
  • 29