Use Apostrophes To Accurately Enter Long Numbers In Excel

ExcelNumberError.jpg The official Excel blog reminds me of a useful shortcut in Excel: if you want to enter something and have it definitely interpreted as text rather than as a number or formula, then precede it with an apostrophe (the single quote symbol ' if you skipped grammar and punctuation in school). I already knew this could be useful if you want to avoid number formatting, or if you want to begin a cell with characters like a hyphen or greater than symbol. What I didn't realise was that it was also essential if you're entering numbers with more than 15 digits, since Excel can't handle these properly and actually changes their value. In the illustration above, the number 1234567812345678 has been entered into both cells, but is preceded by an apostrophe the second time. Notice that not only has Excel used scientific notation in the first cell, but has actually changed the value to 1234567812345670, as you can see in the edit bar. For Excel users, that's worth watching out for, especially if you're entering credit card number, (which have 16 digits. (You can't easily perform calculations on numbers stored as text, but in this case accuracy is clearly more important.)


Be the first to comment on this story!

Trending Stories Right Now