Results 1 to 19 of 19
-
04-26-2007, 08:43 PM #1Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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?
-
04-27-2007, 12:17 PM #2Web Hosting Master
- Join Date
- Dec 2005
- Location
- England, UK
- Posts
- 659
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.█ SupportPal - Smart self-hosted help desk software
Supporting multiple channels, including Twitter and Facebook. WHMCS integration available.
█ LicensePal - Discounted popular web hosting software licenses
cPanel, InterWorx, SolusVM, CloudLinux, Blesta, Softaculous, Installatron, and much more!
-
04-27-2007, 02:17 PM #3Web Hosting Master
- Join Date
- Feb 2003
- Location
- AR
- Posts
- 2,382
Change the cell format to Text and not number.
This will make excel interpret it exactly as entered.
-
04-27-2007, 08:03 PM #4Managed Hosting Expert
- Join Date
- Jan 2004
- Location
- North Yorkshire, UK
- Posts
- 4,164
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).
-
04-28-2007, 11:09 AM #5Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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.
-
04-28-2007, 11:12 AM #6Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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.
-
04-28-2007, 11:23 AM #7Web Hosting Master
- Join Date
- Oct 2001
- Location
- Ohio
- Posts
- 8,535
Change the formatting to number, with 0 decimal places. That'll fix it.
-
04-28-2007, 12:06 PM #8Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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.
-
04-28-2007, 01:53 PM #9Web Hosting Master
- Join Date
- Dec 2005
- Location
- England, UK
- Posts
- 659
█ SupportPal - Smart self-hosted help desk software
Supporting multiple channels, including Twitter and Facebook. WHMCS integration available.
█ LicensePal - Discounted popular web hosting software licenses
cPanel, InterWorx, SolusVM, CloudLinux, Blesta, Softaculous, Installatron, and much more!
-
04-28-2007, 02:37 PM #10Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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!
-
04-28-2007, 02:56 PM #11Web Hosting Master
- Join Date
- Aug 2001
- Posts
- 5,065
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.
-
04-29-2007, 02:58 PM #12Newbie
- Join Date
- Jan 2006
- Posts
- 9
wierd problem
-
04-25-2012, 11:33 AM #13New Member
- Join Date
- Apr 2012
- Posts
- 1
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!!!
-
12-30-2012, 01:32 PM #14New Member
- Join Date
- Dec 2012
- Posts
- 2
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!
-
12-30-2012, 04:04 PM #15is a threadkiller
- Join Date
- Jan 2002
- Location
- Ohio
- Posts
- 3,155
You guys realize this thread is about a half decade old?
Don't like what I say? Ignore me.
-
12-30-2012, 06:20 PM #16New Member
- Join Date
- Dec 2012
- Posts
- 2
Still helpful to anyone who never encountered this before.
-
12-31-2012, 12:20 PM #17Temporarily Suspended
- Join Date
- Aug 2010
- Location
- Kuala Lumpur
- Posts
- 1,632
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
-
12-31-2012, 06:52 PM #18Web Hosting Master
- Join Date
- May 2007
- Posts
- 2,745
Perhaps it was still relevant to a google search amazing though...
Automated, Secure & Low Cost cPanel Backups (on the cloud)
For Users & Web Hosting Providers - User Backups
-
01-22-2013, 12:30 PM #19New Member
- Join Date
- Jan 2013
- Posts
- 1
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 ???