قالب وردپرس درنا توس
Home / Tips and Tricks / To enter a zero before a number in Excel

To enter a zero before a number in Excel



  Excel logo

When numbers are entered in Excel, zeroes are removed at the beginning of the number. This can be a problem when entering phone numbers and IDs. This article describes ways to solve this problem and maintain leading zeros.

Maintaining the leading zero during input

If you want to ensure that the leading zero is maintained as you type, enter a single quote before you enter the number.

  A quote in front of the phone number

This instructs Excel to save the value as text rather than as a number.

When you press Enter to confirm, a green triangle appears in the box, upper left corner of the cell. Excel checks whether you intend to do this or whether you want to convert it to a number.

Click the hash icon to display a list of actions. Select Ignore Error to continue and save the number as text.

 Phone number saved as text in Excel

The green triangle should disappear.

Applying Formatting to Keep Null [19659004] It's quick and easy to apply text formatting as you type, but it's even more efficient to do so in advance.

Select the range of cells you want to format as text. Then click on the "Start" tab, select the list arrow in the number group and select "Text".

 Format an area in advance as text.

The values ​​that you enter in this format The area is now automatically saved as text and leading zeros are preserved.

Using custom formatting to keep it numeric

The two previous options are great and sufficient for most needs. But what if you need it as a number to do some calculations?

You may have an ID number for invoices that you have in a list. These ID numbers are exactly five characters long for consistency reasons, such as 00055 and 03116.

To perform basic calculations, e.g. For example, to add or subtract one to automatically increase the invoice number, you would need to save it as a number to do such a calculation.

Select the range of cells you want to format. Right-click the selected area and click Format Cells.

 Format cells from the context menu

On the Number tab in the category list, select Custom and enter 00000 into the Type field.

 Setting Up Custom Number Formatting

Entering the five zeros forces a fixed-length number format. If only three numbers are entered in the cell, two extra zeros are automatically appended to the beginning of the number.

 Fixed-Length Number Formats

You can play around with custom number formats to get the exact format you need.


Excel's ability to format cells is an excellent tool for ensuring consistent formatting of phone numbers, credit card numbers, and IDs, especially when multiple people enter data.


Source link