Tip of the week # 543
22 May 2018

How to put a zero in front of a number (Microsoft Excel) ^*

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 01223214177 should not be turned into 1223214177. 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:

1. 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 tips

Tip # 193: display your numbers with leading zeros (tip_193.html)

.

^* Unless stated otherwise, these tips were written for Microsoft Office 2010.