1

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.

11
  • Are you opening the CSV file in Excel directly? The preferred method is to use "Get External Data"
    – wysiwyg
    Mar 2, 2017 at 23:51
  • yes I am using microsoft excel directly.
    – maxspan
    Mar 2, 2017 at 23:52
  • So try using Get External Data -> From Text instead. It's on the Data tab. Select Text for Column Data Format
    – wysiwyg
    Mar 2, 2017 at 23:55
  • I tried to store as Text wont work. A zero is still trailing
    – maxspan
    Mar 3, 2017 at 0:05
  • It works for me, just tested it. Again, you need to use Get External Data. Are you doing that?
    – wysiwyg
    Mar 3, 2017 at 0:07

3 Answers 3

3

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: enter image description here

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

2
  • But this will create new file and needs to be saved as excel sheet not csv. I want to save it as csv. If I save it as csv the number are back converted to trailing zeros.
    – maxspan
    Mar 3, 2017 at 0:41
  • I don't know what you mean. I can save the file from Excel as a CSV and it keeps the full numbers just fine. What are you trying to do exactly? Can you provide a screenshot of how the CSV file looks? Just make sure to replace the CC info with dummy data...
    – wysiwyg
    Mar 3, 2017 at 1:34
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.

8
  • 1
    so whats the solution
    – maxspan
    Mar 2, 2017 at 23:58
  • @maxspan honestly, use smalller numbers.
    – Ramhound
    Mar 2, 2017 at 23:59
  • I want to add credit card numbers mate
    – maxspan
    Mar 3, 2017 at 0:00
  • try superuser.com/questions/373997/… Mar 3, 2017 at 0:00
  • 2
    @maxspan Storing CC numbers in an excel spreadsheet is likely to be against the data protection rules in a lot of places ...
    – DavidPostill
    Mar 3, 2017 at 10:32
1

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

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