hosted by liquidweb


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

Forum Jump

Excel keeps changing numbers! How to fix this?

Reply Post New Thread In Computers and Peripherals Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699

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?


__________________
Juliet



Sponsored Links
  #2  
Old
Web Hosting Master
 
Join Date: Dec 2005
Location: England, UK
Posts: 623
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  
Old
Web Hosting Master
 
Join Date: Feb 2003
Location: AR
Posts: 2,377
Change the cell format to Text and not number.
This will make excel interpret it exactly as entered.

Sponsored Links
  #4  
Old
Managed Hosting Expert
 
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  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699
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.

__________________
Juliet

  #6  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699
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.

__________________
Juliet

  #7  
Old
Web Hosting Master
 
Join Date: Oct 2001
Location: Ohio
Posts: 8,033
Change the formatting to number, with 0 decimal places. That'll fix it.

  #8  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699
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?

__________________
Juliet


Last edited by Frosty; 04-28-2007 at 12:13 PM.
  #9  
Old
Web Hosting Master
 
Join Date: Dec 2005
Location: England, UK
Posts: 623
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  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699
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!

__________________
Juliet

  #11  
Old
Web Hosting Master
 
Join Date: Aug 2001
Posts: 4,699
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.

__________________
Juliet


Last edited by Frosty; 04-28-2007 at 02:59 PM.
  #12  
Old
Newbie
 
Join Date: Jan 2006
Posts: 9
wierd problem

  #13  
Old
New 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!!!

  #14  
Old
New 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!

  #15  
Old
is useless
 
Join Date: Jan 2002
Location: Ohio
Posts: 3,070
You guys realize this thread is about a half decade old?

__________________
Don't like what I say? Ignore me because it will be the only way you can shut me up.

Reply

Related posts from TheWhir.com
Title Type Date Posted
Breaking Down the Numbers of the Media Temple Acquisition Blog 2014-06-17 17:24:51
Microsoft Depletes US IPv4 Addresses for Microsoft Azure Web Hosting News 2014-06-13 12:37:42
The Final Countdown: ARIN Down to 16 Million IPv4 Addresses Web Hosting News 2014-05-23 15:27:49
Cloud Technologies Drive Demand for IT Service Providers: Report Web Hosting News 2014-05-01 08:33:50
Open Source Collaboration Provider Open-Xchange Launches OX Spreadsheet Tool: WHD.global 2014 Web Hosting News 2014-04-01 11:18:13


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
WHT Host Brief Email:

We respect your privacy. We will never sell, rent, or give away your address to any outside party, ever.

Advertisement:
Web Hosting News:
WHT Membership
WHT Membership



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?