How to prefix cell entry with zero in Excel

This tutorial is about the steps required to prefix cell entry with zero in excel 2016 & 2013. If you enter a number with leading zero, excel would automatically remove the zero.

image

In the above sample data, though the phone number was entered as 09238292267, the leading zero has been removed by excel. You can fix this by the following methods.

Apostrophe

Just before entering the number, prefix the number with apostrophe to include zero as shown in the below screenshot.

image

Change Format

If you have more than one entry that needs to be prefixed with zero then you can change the formatting for those cells to Text instead of adding apostrophes to each cell entry.

image

Select the Column, navigate to Number section in the Home tab and choose the format as Text.

image

Turn off the Errors

image

You might have noticed the display of error because the number is formatted as text or preceded by an apostrophe. These error indicators can be removed by using the option provided as part Formula Settings.

Step 1: Click the File menu and select Options from the menu list.

Step 2: In the Excel Options screen, click the Formula tab.

Step 3: Navigate to the Error checking rules section under Formula screen.

Step 4: Unmark the check box with caption as Numbers formatted as text or preceded by an apostrophe.

image

Step 5: Click Ok to confirm and save the changes.

image

In Category: Excel 2013, Excel 2016

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 2 Comments

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.