Tip of the week # 578
19 July 2019

How to keep the first zero of a telephone number (Microsoft Excel) ^*

Back in May 2018 I told you about one of my bugbears in Excel being that you cannot add a zero in front of a number, which I often want to use for telephone numbers. At the time I told you to type an apostrophe (') in front of the number. But during a recent Excel training session one of my delegates shared his way of tackling the fact that 01223214177 should not be turned into 1223214177.

Here's how:

1. Add a space after the area code.

For example 01223 214177. If it's not a landline, simply add a space anywhere. Say, 07941 848326.

Excel will treat the number as text and will leave it exactly as you typed it.

Thanks for this tip, Francois!

Related tips

Tip # 543: How to put a zero in front of a number - tip_543.php

Tip # 193: Display your numbers with leading zeros - tip_193.html

.

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