I love Excel but there are a couple of things that really annoy me. One of these things is that you cannot add a zero in front of a number, which I often want to use for telephone numbers and invoices. For example 01223123456 should not be turned into 1223123456. And James Bond’s code number should not be changed to 7!
Oh, and how about large numbers such as credit cards that are turned into some weird code? For example 1234567898765432 being displayed as 1.23457E+15.
There are various ways you can tackle this, by means of custom formats and the TEXT Function. But if you never need to use the number in a calculation, there is a much easier way.
Here’s how:
- Type an apostrophe (') in front of the number.
Excel will treat the cell as text (hence the fact that you cannot use it to do a calculation) and the number will be left-aligned. You can obviously reset the alignment by clicking on the Align Text Right button.
Related tip
Tip # 193: Display your numbers with leading zeros
24 May 2018
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk