Getting leading zeros to behave

Just discovered this morning how to get those pesky leading zeros (e.g. in postal codes and phone numbers) in Excel and Google sheets to behave.  My approach in the past has been to convert the filed to a text field and then re-enter the leading zero.  But when copying and pasting, the formatting is often and lost and the pesky leading zero disappears again.

The solution I discovered this morning, which I suspect is well known to everyone who has passed their ICDL spreadsheet module, is to use an apostrophe before the leading zero and then the zero stays put.  So henceforth, all coordinators entering data with leading zeros into the CIH google docs are requested to enter an apostrophe before the leading zero.  This will make life so much easier when working with this data and avoid a lot of wasted time re-inserting the leading zeros.

While we are on this topic, coordinators should also use the recognised convention for listing telephone numbers, e.g. for a mobile:  027 xxx yyyy or for a landline 06 xxxx yyyy

3 thoughts on “Getting leading zeros to behave

  1. I just put a space after the third digit, and the leading zero stays put. (027 0000000) The phone number in Column K behaves. I don’t know why the others don’t. I was hoping you’d come up with a solution, Laurence.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s