0

I want to convert a whole column of a csv opened excel in which a column have some numbers larger than 16 digits so i want to preserve those last digits from rounding off as excel does that with long numbers.
I tried selecting the whole column then clicking on "format cells" and from their changing the number's category from general to text.But i have to double click once on every cell in that column to confirm the change in that cell and problem is, there are thousands of rows in that cloumn.

I destroyed a csv file thinking the cloumn had been altered once i did the "format cells" and upon saving excel changed every big number to an equivalent exponential form. for example 123456789123456789 was changed to 1.234E17(123400000000000000).The cells on which i doubled clicked was saved though.
I don't want that to happen to my other files.

  • Are the original BIG numbers in a .csv file or in an Excel worksheet?? – Gary's Student Sep 23 '17 at 20:26
  • they are in the csv – user204682 Sep 23 '17 at 20:40
1

Try this:

Select the column containing the long numbers.

Then, in the home bar, under the numbers section, press on the drop down list and select "custom".

Then go to the category "numbers" and select "use 1000 separator. That should do the trick.

Also you can try to just make the column wide enough to present the whole number.

  • That won't solve the floating point limit. To preserve all the digits, they need to be converted to text. – fixer1234 Sep 25 '17 at 4:06
  • Hey, to my understanding there is a csv file contains numbers. After pasting them in excel they are presented in exponential form. I tried it at home and the steps I wrote above worked for me fine- all the big numbers were presented completely. Is their a problem I'm missing? Thanks for the feedback. – dshaviv Sep 25 '17 at 4:53
  • 1
    Regardless of where the number comes from, if Excel is using the default precision and treating something as a number, it is limited to 16 digits. If you import long strings of digits and manage to store them in cells, and Excel hasn't yet tried to handle them as numbers, the digits may remain temporarily. However, the extra digits will be lost (or ignored) as soon as Excel tries to handle them as numbers. – fixer1234 Sep 25 '17 at 5:11
  • I see, good to know – dshaviv Sep 25 '17 at 5:20
  • it kind of did work but it added commas. and commas reflected in csv as well but as i was going to use the csv as the temporary database for my program. I could tweak it to neglect the commas.So thanks!! – user204682 Sep 25 '17 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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