Results 1 to 19 of 19
  1. #1
    Join Date
    Aug 2001
    Posts
    4,822

    Excel keeps changing numbers! How to fix this?

    I have an excel mystery which I have been trying to fix for a VERY long time now to no avail. HOPING somebody can help solve it

    For example I had to temporarily export a billing database (list of credit card numbers) into wordpad in order to then transfer it into my new billing system. In wordpad the numbers show up just fine BUT I needed to open it in excel to format the database a little before transporting it into my new billing system.

    BUT when I open it in excel... excel changes all the card numbers! For example if your card # was:

    44445959636345

    Instead of showing up as shown above... excel changes it and makes it show up as:

    4.4446E+13

    If I format the cell to show as "number" to try to fix this... it will then place decimals in the number AND it will replace the last digit of the card number with a zero (o) making the card number completely invalid!! If I tell it to place NO decimal when formatting the cell to show as "number" it will STILL delete the last digit of the card number and replace it with a zero.

    For the life of me I cannot figure out why excel keeps changing numbers like this? Does anybody know how to correct this?


  2. #2
    Join Date
    Dec 2005
    Location
    England, UK
    Posts
    625
    Quote Originally Posted by Frosty View Post
    44445959636345

    Instead of showing up as shown above... excel changes it and makes it show up as:

    4.4446E+13

    If I format the cell to show as "number" to try to fix this... it will then place decimals in the number AND it will replace the last digit of the card number with a zero (o) making the card number completely invalid!! If I tell it to place NO decimal when formatting the cell to show as "number" it will STILL delete the last digit of the card number and replace it with a zero.
    I actually have a similar problem to this but it's easily fixed by choosing number with no decimals. I just tried the number you had and it works perfectly fine unlike how you said.

    I'm wondering if anyone has a way for this not to happen from the start? I haven't had a chance to look in options but would be grateful if anyone knows.
    ArcticDesk - Beautifully engineered customer support help desk software
    Supporting multiple channels, including Twitter and Facebook. WHMCS integration available.
    LicensePal - Discounted popular web hosting software licenses
    cPanel, ClientExec, Blesta, SolusVM, CloudLinux, Softaculous, Installatron, and more!

  3. #3
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    Change the cell format to Text and not number.
    This will make excel interpret it exactly as entered.

  4. #4
    Join Date
    Jan 2004
    Location
    North Yorkshire, UK
    Posts
    4,163
    Excel only supports 15 digit whole numbers, it will convert them if you enter a longer number.

    You need to select text as the field type.

    Dan
    █ Dan Kitchen | Technical Director | Razorblue
    █ ddi: (+44) (0)1748 900 680 | e: dkitchen@razorblue.com
    █ UK Intensive Managed Hosting, Clusters and Colocation.
    █ HP Servers, Cisco/Juniper Powered BGP Network (AS15692).

  5. #5
    Join Date
    Aug 2001
    Posts
    4,822
    No, formatting it to text does not work.

    I just imported my database (which shows up perfectly fine in notepad/wordpad)... I just imported this into excel. It changed all the credit card numbers to:

    4.4446E+13

    If I tell it to format to show up as text... it does NOTHING. The numbers remain as shown above...

    I just think this is absurd that excel goes an changes numbers like this. I think this is ridiculously designed that it goes and changes numbers like this. It should leave it as is.

  6. #6
    Join Date
    Aug 2001
    Posts
    4,822
    Is there anyway to tell it to NOT do this? This is crazy. Yes, I just noticed that's why it's doing it... when you enter more than 15 digits of numbers... it messes it up!!

    If you test it out by opening excel and entering a single card number... and then formatting it to text. It fixes it.

    But NOT when you have a database imported... formatting it then does not work. The numbers remain messed up. I tried formatting the entire column to show as text... that didn't work. I then clicked on each card number individually to format as text... that didn't work.

  7. #7
    Join Date
    Oct 2001
    Location
    Ohio
    Posts
    8,082
    Change the formatting to number, with 0 decimal places. That'll fix it.

  8. #8
    Join Date
    Aug 2001
    Posts
    4,822
    inogenius,

    That doesn't work either. The numbers remain messed up in my database. It doesn't work when you format it to show "number" with no decimal. because then it drops the last digits and replaces it with a zero.

    I just tested it. Open up excel and type the below as your test card number:

    1234123412341234

    Excel will then change it to:

    1.23412E+15

    If you then format to show as number without decimal it will show up as:

    1234123412341230

    It's messed up!

    What the heck did it do with the last digit? It replaced it with a zero! maybe I can call support... see if they have the answer?
    Last edited by Frosty; 04-28-2007 at 12:13 PM.

  9. #9
    Join Date
    Dec 2005
    Location
    England, UK
    Posts
    625
    Dan said before that Excel only supports 15 digits whilst your number is 16. That is why. You might still need to call support for a solution though.

    Quote Originally Posted by Frosty View Post
    inogenius,

    That doesn't work either. The numbers remain messed up in my database. It doesn't work when you format it to show "number" with no decimal. because then it drops the last digits and replaces it with a zero.

    I just tested it. Open up excel and type the below as your test card number:

    1234123412341234

    Excel will then change it to:

    1.23412E+15

    If you then format to show as number without decimal it will show up as:

    1234123412341230

    It's messed up!

    What the heck did it do with the last digit? It replaced it with a zero! maybe I can call support... see if they have the answer?
    ArcticDesk - Beautifully engineered customer support help desk software
    Supporting multiple channels, including Twitter and Facebook. WHMCS integration available.
    LicensePal - Discounted popular web hosting software licenses
    cPanel, ClientExec, Blesta, SolusVM, CloudLinux, Softaculous, Installatron, and more!

  10. #10
    Join Date
    Aug 2001
    Posts
    4,822
    When he said excel only supports 15 digits... I thought that if I formatted the column a certain way I could make it support 16 digits (since credit cards have 16 digits). I need to quickly import my database into excel... to rearrange the database so that I can then import to my billing system at authorizenet.

    I'll call excel support and see if they know of a solution this this. I just feel that is is completely absurd that excel only supports 15 digits and that if you put in more than 15 digits it completely alters/messes up your numbers. It's strange!

  11. #11
    Join Date
    Aug 2001
    Posts
    4,822
    Whho hoo! I called support and thy solved the problem

    Here is the solution. By default whenever you open a database in excel it imports the database in GENERAL format. Once it imports the database in that default GENERAL format you CANNOT format it to make it display the numbers correctly no matter what.

    You MUST import the database in the correct format... you can't change it later. The card numbers will remain messed up.

    So the VERY simple solution is... when you click OPEN and start importing your database... the very last option... you will see that GENERAL is checkmarked by default and it will by default import your database in GENERAL format. You must check TEXT instead. It will then import your database via TEXT format... and then it displays 16+ digits just fine with no problem.

    So it's not that excel only supports 15 digits... it's that you were simply opening the database in the WRONG type of format... once you do... you cannot format the column and change it to TEXT once you already imported your database... then it's already too late.

    Geez.... that was simple solution. Glad it works.
    Last edited by Frosty; 04-28-2007 at 02:59 PM.

  12. #12
    wierd problem

  13. #13

    same problem when creating a database....

    I encountered the same issue when creating a database that wasn't merged. I had several columns of info entered and when I came to the one that had a cred card number in it, I was getting the same result b/c it was 16 digits. To change this, right click on the column you need to change. When the drop down box appears, click on Format Cells. The first box that opens will show Category and "General" is the first one listed. Two up from the bottom of the list of options you will see "Text". Click on that and click OK. This will set your whole column to accept 16-digit (or any number higher than 15 digits) numbers as they are entered...

    Thank you for your help!!!

  14. #14
    Andria, your post solved my problem. I had tried changing the formatting to text after typing the number in the appropriate cell, but it had now effect. When I deleted the number and reformatted the column to text, I was able to type and save the correct number. Thanks!

  15. #15
    Join Date
    Jan 2002
    Location
    Ohio
    Posts
    3,131
    You guys realize this thread is about a half decade old?
    Glioblastoma Multiforme (GBM) Brain Cancer Awareness. May is Brain Cancer awareness month. Gray Matters!
    Incurable, 6-18 months prognosis, survivors longer than 3 years less than 1% chance.
    Don't like what I say? Ignore me.

  16. #16
    Still helpful to anyone who never encountered this before.

  17. #17
    Join Date
    Aug 2010
    Location
    Kuala Lumpur, Malasiya
    Posts
    1,610
    People answering to the thread which is half a decade old

    Anyways those guys who are looking at this thread for solution, here it is: http://support.microsoft.com/kb/269370
    Hope this helps
    Shinjiru Technology Inc. No. 1 Offshore Web Hosting Provider since 1998
    Strongbolt Hosting, Private Email, VPS, Dedicated Servers and More.
    Malaysia, Singapore, Holland, Russia, Luxembourg, Bulgaria, Lithuania, Hong Kong
    Email: sales@shinjiru.com | Skype: Shinjiru121

  18. #18
    Join Date
    May 2007
    Posts
    2,554
    Perhaps it was still relevant to a google search amazing though...
    I have opinions just like you do!

  19. #19

    Question Still relevant yes - excel changes number and makes errors in the record

    Excel has not made any attempt to solve the serious problem that card numbers of 16 digits and adresses are being changed by Excel.

    If I write "Oak Street 15-2" in two columns, so that "Oak Street" is in A1 and "15-2" is in B1, then ....

    Try it - you may get a very strange house number indeed !!!

    It should not be so difficult to change

    1) If there is no "=" in the start EXCEL should always use TEXT FORMAT

    2) Any other ideas ???

  20. Newsletters

    Subscribe Now & Get The WHT Quick Start Guide!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •