Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to show long numbers in Excel?

Tags:

c#

excel

I have to build a C# program that makes CSV files and puts long numbers (as string in my program). The problem is, when I open this CSV file in Excel the numbers appear like this:

1234E+ or 1234560000000 (the end of the number is 0)

How I retain the formatting of the numbers? If I open the file as a text file, the numbers are formatted correctly.

Thanks in advance.

like image 268
Gold Avatar asked May 24 '09 19:05

Gold


2 Answers

As others have mentioned, you can force the data to be a string. The best way for that was ="1234567890123". The = makes the cell a formula, and the quotation marks make the enclosed value an Excel string literal. This will display all the digits, even beyond Excel's numeric precision limit, but the cell (generally) won't be able to be used directly in numeric calculations.

If you need the data to remain numeric, the best way is probably to create a native Excel file (.xls or .xlsx). Various approaches for that can be found in the solutions to this related Stack Overflow question.

If you don't mind having thousands separators, there is one other trick you can use, which is to make your C# program insert the thousands separators and surround the value in quotes: "1,234,567,890,123". Do not include a leading = (as that will force it to be a string). Note that in this case, the quotation marks are for protecting the commas in the CSV, not for specifying an Excel string literal.

like image 103
John Y Avatar answered Oct 09 '22 03:10

John Y


Format those long numbers as strings by putting a ' (apostrophe) in front or making a formula out of it: ="1234567890123"

like image 38
VVS Avatar answered Oct 09 '22 05:10

VVS