You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
If you use Excel to track identification numbers (serial numbers, tracking numbers, etc.) you may find that if the IDs have more than 15 digits, Excel loses the digits after the 15th and makes them all zero. In other words, type "123456789123456789" into a cell and Excel will show you "123456789123456000". What's going on? Is this a bug?! Nope. You've hit an Excel limit with how large of a number Excel can handle when performing math operations on a number.
But in this case, we don't care about performing math on things like tracking numbers. The solution? Don't make it a number at all. Select the cells where you will be entering these identification numbers, right-click, select Format Cells and choose the Text option in the Number tab. This tells Excel to treat the number like normal alphanumeric text, which means they will display exactly as you enetered them, but you can't really use the value represented in the string as inputs to formulas (I'm over simplifying here a bit - those formula savvy among us know there are ways around this, but that's beyond the point of this discussion).
There's a shortcut too, if you type an apostrophe before entering that long tracking number, that's a signal to Excel to treat the value as Text.
Comments: (9) Collapse
Incorrect. You should precede the number with an apostrophe, NOT a hyphen. That simply leads to a negative version of the number
i.e. "-123456789123456000"
The displayed string will not show the leading apostrophe.
I bet you meant apostrophe (not hyphen).
'123456789012345678
not
-123456789012345678
Oops! Thanks for catching that. I'll update the post now.
I hope nobody ever needs a value that large. For tracking code, the solution is acceptable but mathematical operation that will be a user nightmare.
Thanks for sharing.
"serial numbers, tracking numbers, etc."
You left out the most important variation. The only time I've ever been asked about this is when someone was trying to enter a credit card number.
Sumif and countif cannot distinguish numbers stored as text beyond the 15th digit
so if try and count how many times the below mentioned number(stored as text)
1234567812345678
is appearing in a range of cells containing
1234567812345679
1234567812345677
Countif will return 3
To get the correct answer you will have to use
=sumproduct(--(Rang of cells = Number))
You would have expected MS to fix this bug/feature in Countifs and sumifs in 2007... instead they just carried it forward from Sumif/Countif...after all the "real" users told them that they wanted a larger paste button
Jon: good point!
sam: interesting, I wasn't aware of that. Thanks for sharing.
Here's a brief comparison of what will and won't work using a formula approach in lieu of a formatting-based solution. Formatting is an invisible solution. A formula-based approach is more substantial.
This solution works if the 16 (or more) digit number can be split into two or more input cells. This is a common approach for long numbers such as credit card account numbers.
The solution is to use TEXT. If you try to join the two parts within one TEXT formula, it won't work. Excel will impose the 15 digit limit. Example, if the two parts are input in cells C2 and D2, then TEXT(C2 & D2,"0") will not beat the 15 digit limit.
However, TEXT(C2,"0") & TEXT (D2,"0") will beat the limit. I tested this with two inputs, each 15 digits, and results were expressed as a 30 digit string of properly joined inputs.
16 digit credit card numbers can be managed by entering them in 4 cells that are joined into a single string, using a single-dash spacer, if needed for readability. In this second example, the 4 segments of 4 digits are entered to C2, D2, E2, and F2 while the dash is stored in a single-cell range named "dash": =TEXT(C2,"0") & DASH & TEXT(D2,"0") & DASH & TEXT(E2,"0") & DASH & TEXT(F2,"0").
The string expressed by this formula is 19 characters long. This example excludes consideration of data validation, which could be applied in this case with a series of function-based tests, e.g., testing the 1st digit of the the overall number for "4" if a VISA account is expected, vs. a "5" for M/C or a "6" for Discover.
With format set to text and apostrophes, there is a little triangle in the upper left corner.
How do I turn that off?
Comments: (loading) Collapse