Tame your computer - zero in on Excel


In her regular series for Cambridge Network members, software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she explains 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 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:

  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 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



Karen Roem
Microsoft Office-ionado

Roem Ltd – software training and support