Getting trailing zero in csv file for 15 digit number

Question

I am having a column in csv with 15 digits with value 1234567812345678 but it always coverts the last digit with zero 1234567812345670. I have formatted them as number.

Answer 1

As Sir Adelaide answered, Excel supports number precision only up to 15 digits. But you can still save numbers with many more digits if you store it as text.

When you open a CSV file in Excel directly, Excel will automatically convert your numbers to number format, causing the truncation. Use Get External Data to bypass this:

In a new Excel file, go to the Data tab, and click From Text in the Get External Data section. Navigate to your CSV file and hit Open.

Now choose Delimited, check My data has headers if it does, hit Next.

On the next screen, assuming your delimiter is a comma as it is a CSV, make sure only Comma is checked and hit Next

This is the crucial step: Select the column(s) in the Data Preview section with the long numbers and choose Text:

Then just hit Finish, select where you want to put your data, and you're done!

Answer 2

You've run into the maximum precision that Excel can handle (15 digits). See

https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2010

for more detail.

Answer 3

Excel seeing you number as a number, just enclose this long number in double quotes (like:"1234567812345678") and it would be treated as a text, so you can keep there even longer numbers for credit cards that may be will appear in a future.

Example:

name,card_number,expiration_date
John,"1234567812345678","2017-03-03"

Another way to get Excel tricked is to represent card number separated with dashes as 1234-5678-1234-5678 that will be treated by Excel as text too