 |
|
| TheExcelAddict.com | |
| December 4, 2019 | |
| Hi fellow Excel Addict, | |
| I hope you're having an EXCEL-lent week!! It may be a cold and rainy December here in eastern Newfoundland but thankfully we haven't had to deal with any snowstorms yet.  This week's 'Excel in Seconds' tip 'Prevent Excel From Changing End of 16+Digit Numbers to Zeros' offers a simple solution to an issue that often puzzles and frustrates Excel users. If you like my 'Excel in Seconds' tip this week, please share it with your colleagues and any other Excel users you know and recommend that they sign up for my newsletter. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Prevent Excel From
Changing End of 16+Digit Numbers to Zeros |
|
In the normal course of using Excel, most of us never need to use 16-digit numbers in our calculations. Did you know that a 16-digit number is more than a quadrillion? 1,000,000,000,000,000 = 1
Quadrillion
Who uses numbers that big?! But if you need to enter long numbers such as credit card numbers, social insurance numbers and account numbers into Excel, you may run into some trouble. You will find that Excel replaces one or more of the last digits of those 16+ numbers with zeros. Just try it yourself. Type a number longer than 15 digits into your worksheet and see what happens to the digits after the 15th.  Excel isn't designed to handle numbers larger than 15 digits. So when you type a value with more than 15 digits into a cell containing a number format, Excel stores only the first 15 significant digits and changes all remaining digits to zeros. Since these 16+digit numbers aren't going to be used for calculations, you should think of them as text strings. A cell can contain only up to 15 digits for numbers but up to 32,767 characters for text. So if you format your cells as Text before entering the 'numbers', your problem will be solved. Apostrophe Solution If you're entering just one or a few credit card numbers, one option is to precede the number with an apostrophe ('). So you simply type an apostrophe and then your long number. This will also retain leading zeros on numbers.  Text Format Solution However, when you are dealing with a column of long numbers, it's usually easier to format the column as Text so you don't have to type an apostrophe before each number. This too retains leading zeros on numbers. If you have already been entered the numbers into a worksheet and the last digits have changed to zeros, there is no way to change them back. You will need to format these cells as Text and re-enter the 16+ digit numbers. To format cells to treat numbers as Text... 1) Select the cells that will contain the long numbers; 2) On the Home tab, click the Number Format dropdown; 3) Select Text. 4) Then enter the numbers. If you are copying and pasting numbers, you will need to right click and choose Paste Special, Values or Paste, Text to avoid overwriting the Text formatting in the cells.  |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |