Web Hosting Talk : Other Forums : Computers and Peripherals : Excel keeps changing numbers! How to fix this?

[Frosty]

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?

[LP-Jay]

Originally Posted by Frosty

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.

[ThatScriptGuy]

Change the cell format to Text and not number.
This will make excel interpret it exactly as entered.

[dkitchen]

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

[Frosty]

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.

[Frosty]

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.

[IGobyTerry]

Change the formatting to number, with 0 decimal places. That'll fix it.

[Frosty]

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?

[LP-Jay]

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.

Originally Posted by Frosty

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?

[Frosty]

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!

[Frosty]

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.

[ahmedbakair]

wierd problem

[andria1079]

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!!!

[Imrick]

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!

[MGCJerry]

You guys realize this thread is about a half decade old?