Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2016, 08:13 PM
johnharbo johnharbo is offline Excel error in inputting long numbers Mac OS X Excel error in inputting long numbers Office for Mac 2011
Novice
Excel error in inputting long numbers
 
Join Date: Mar 2016
Posts: 5
johnharbo is on a distinguished road
Default Excel error in inputting long numbers

I've encountered what appears to be a bug in both Excel for Mac 14.6.1 and 15.19.1.

When you format a cell to be a number (no decimals), and then paste a credit card number in that cell, and hit return/enter, it deletes the last digit and replaces it with a "0".

So for example, 1234567891234567 shows up as 1234567891234560.

or 123456789123456789 shows up as 123456789123456000.

The same thing happens if you type the number in to the cell.

Obviously, I can circumvent if I set it up as a text field by typing '1234567891234567.



Does anyone know why this occurs?

John
Reply With Quote
  #2  
Old 03-08-2016, 01:38 AM
xor xor is offline Excel error in inputting long numbers Windows 10 Excel error in inputting long numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

There is always Excel Help!
Try to search Excel specifications, Calculation specifications and limits.
Reply With Quote
  #3  
Old 03-08-2016, 06:24 AM
gebobs gebobs is offline Excel error in inputting long numbers Windows 7 64bit Excel error in inputting long numbers Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Can you format as ####################?
Reply With Quote
  #4  
Old 03-08-2016, 06:59 AM
johnharbo johnharbo is offline Excel error in inputting long numbers Mac OS X Excel error in inputting long numbers Office for Mac 2011
Novice
Excel error in inputting long numbers
 
Join Date: Mar 2016
Posts: 5
johnharbo is on a distinguished road
Default

Setting format as #################### does not work. Still changes the last digit to 0
Reply With Quote
  #5  
Old 03-08-2016, 07:42 AM
xor xor is offline Excel error in inputting long numbers Windows 10 Excel error in inputting long numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

You don't trouble to read what I wrote?
Reply With Quote
  #6  
Old 03-08-2016, 06:56 PM
johnharbo johnharbo is offline Excel error in inputting long numbers Mac OS X Excel error in inputting long numbers Office for Mac 2011
Novice
Excel error in inputting long numbers
 
Join Date: Mar 2016
Posts: 5
johnharbo is on a distinguished road
Default

When I put those words in excel help search box, as well as various subsets of those words, I get back "no results".

So I don't know how to see what you are referring me to
Reply With Quote
  #7  
Old 03-08-2016, 10:21 PM
xor xor is offline Excel error in inputting long numbers Windows 10 Excel error in inputting long numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Calculation specifications and limits


Feature

Maximum limit



Number precision

15 digits


Smallest allowed negative number

-2.2251E-308

..............
Reply With Quote
  #8  
Old 03-09-2016, 01:36 AM
macropod's Avatar
macropod macropod is offline Excel error in inputting long numbers Windows 7 64bit Excel error in inputting long numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It is not an error, it's a documented limitation that has been around for many years. The 15-digit resolution limit is part of the Excel specifications, which XOR suggested you read. See: https://support.office.com/en-us/art...7-269d656771c3 - returned by inputting 'Excel specifications, Calculation specifications and limits' into a web search. It also explains why the format suggested by gebobs won't work.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 03-09-2016, 03:50 PM
johnharbo johnharbo is offline Excel error in inputting long numbers Mac OS X Excel error in inputting long numbers Office for Mac 2011
Novice
Excel error in inputting long numbers
 
Join Date: Mar 2016
Posts: 5
johnharbo is on a distinguished road
Default

Thanks!

I guess Microsoft doesn't expect anyone to input a credit card number.
Reply With Quote
  #10  
Old 03-09-2016, 04:06 PM
macropod's Avatar
macropod macropod is offline Excel error in inputting long numbers Windows 7 64bit Excel error in inputting long numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

This has nothing to do with what Microsoft 'expects', but everything to do with how a computer stores numbers. One might also argue that credit card 'numbers' aren't numbers - they're a string consisting of digits with space separators. Excel allows you to input them as string.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 03-09-2016, 04:12 PM
johnharbo johnharbo is offline Excel error in inputting long numbers Mac OS X Excel error in inputting long numbers Office for Mac 2011
Novice
Excel error in inputting long numbers
 
Join Date: Mar 2016
Posts: 5
johnharbo is on a distinguished road
Default

got it. Thanks
Reply With Quote
  #12  
Old 03-09-2016, 09:35 PM
sdrawkcab sdrawkcab is offline Excel error in inputting long numbers Windows 7 32bit Excel error in inputting long numbers Office 2003
Novice
 
Join Date: Dec 2015
Posts: 4
sdrawkcab is on a distinguished road
Default

Any "number" that is going to be used for mathematical usage is subject to a 15 digit limit. If said number is to be used as an identifier only, then format as text string (no constraints are placed on said number length.)
Reply With Quote
  #13  
Old 03-09-2016, 10:04 PM
macropod's Avatar
macropod macropod is offline Excel error in inputting long numbers Windows 7 64bit Excel error in inputting long numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I wouldn't go so far, since it's only an issue for numeric strings of more than 15 digits and, furthermore, Excel accepts shorter numbers (not strings) as the input for formatting as phone numbers, zip-codes, etc.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel error in inputting long numbers Inputting a number into word in a specific format Jo 4x4 Word 7 07-19-2014 12:45 AM
Excel error in inputting long numbers Automation error Unknown error" message once they open the Excel file hlina Excel 1 10-08-2013 09:14 PM
Renaming Word Formfields: string too long error silverspr Word VBA 7 01-22-2013 06:20 PM
Inputting Sound files - 2 options, neither working. msteinruck PowerPoint 0 09-16-2010 09:44 AM
Inputting variable data axy Word 0 09-08-2009 04:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft