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.
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
My data has headers if it does, hit
On the next screen, assuming your delimiter is a comma as it is a CSV, make sure only
Comma is checked and hit
This is the crucial step: Select the column(s) in the
Data Preview section with the long numbers and choose
Then just hit
Finish, select where you want to put your data, and you're done!
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. 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...– wysiwygMar 3, 2017 at 1:34
You've run into the maximum precision that Excel can handle (15 digits). See
for more detail.
1so whats the solution Mar 2, 2017 at 23:58
@maxspan honestly, use smalller numbers.– RamhoundMar 2, 2017 at 23:59
I want to add credit card numbers mate 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
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.
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
Get External Data->
From Textinstead. It's on the
Column Data Format