If you have ever come across this it can be confusing and damn frustrating, you type a long number into Excel and weirdly anything past digit 15 is placed as zeros. So if you are typing a credit card number, which are normally 16 digits then the last is displayed as a zero.
This is because Excel only stores 15 significant digits in a number, then changes the any remaining to zeros. This occurs because Excel interprets the numbers as being intended for calculation as the cells are formatted as numbers. OK, so can we just reformat them as text or something else, well no, once entered, you cannot just reformat. You will need to format new cells as Text then type in the numbers again.
Hmmm, so is there anything else we can do to avoid having to re enter all of those numbers?…there are a few things.
1. Put a leading ‘ in front of you number. so for example ’2560089996999987 would display ok.
2. If you are importing your data, then from a .rtf or.csv file then when the text import wizard appears, scroll to your field that contains the offending ‘longer than 15 digits’ number. Click on it then in the upper right hand section of the window change the field of column type from ‘General’ to ‘Text’.
I always make sure to check out any data I am importing to see if this issue may occur, as it is easier to catch it at import stage than having to either get a leading apostrophe or re type…oh nasty…..
Other Excel Posts You Might Like
1. Change The Color Of Excel Borders
2. Create Self Expanding Chart In Excel- Video
3. SUM only negative numbers in an Excel Range
4. Data Validation To Control Data Entry
