5 Ways To Add Leading Zeros To Numbers In Excel

When entering data such as phone numbers, employee codes, etc., it can be frustrating when Excel automatically deletes leading zeros that you want to keep. How do you add zeros at the beginning of a phone number in Excel? Let’s explore the following ways to add zeros in Excel together on Buffcom.net.

Why Add Zeros in Excel?

Have you ever tried to enter data like a phone number or employee code that starts with one or more zeros, such as 0094536416, into Excel? Excel may automatically remove any leading zeros from any number sequence, no matter how hard you try to enter them.

If you want to keep leading zeros in your data, it can be inconvenient, and you may not know why or how to make Excel keep or add zeros in Excel.

Don’t worry, in this article, you will learn nine different ways to add zeros at the beginning of number sequences in Excel.

How to Keep Zeros in Excel When Entering Numbers

Method 1: Add a Single Quote Mark (‘) at the Beginning of the Number Sequence Before Entering Data.

Example: Instead of entering a phone number as 0123456789, enter ‘0123456789. Excel will keep the leading zero of the phone number without deleting it. This is the quickest and simplest way to add zeros in Excel, and the data you enter will automatically be in Text format.

Method 2: Convert the Cell Format to Text

Select the cell or range of cells that you want to keep leading zeros. In the Home tab, under the Number section, select Text as the cell format instead of the default General format.

After converting the cell format to Text, a triangle with an exclamation mark will appear in the top-left corner of the cells. You just need to select all the cells -> Click on the yellow triangle -> Ignore Error, and the triangles will disappear.

Excel will treat all data you enter as Text, even if they are numbers. This will cause Excel to keep all leading zeros of your numbers.

Note: The two methods above format the text string, which will not be treated as a number and cannot be used for calculations. After entering the data, if you want to use these text strings for calculations, change the cell format back to Number.

How to Add Zeros at the Beginning of Numbers in Excel with Existing Data

If you have an Excel file with existing data, and you want to add zeros to a batch of number sequences without having to edit each cell, you can apply one of the following methods:

Method 3: Adding Zeros in Excel by Customizing Cell Format with Format Cells

Step 1: Select the cell or range of cells where you want to add zeros to the beginning of the number sequence. Open the Format Cells window using one of the three methods:

• At the Home tab, click on the drop-down arrow of the Number category.
• Right-click on the selected cells and choose Format Cells.
• Press Ctrl + 1.

Step 2: Go to the Number tab, select Custom and add a new custom format as desired.

For example, if you want to add two zeros to the beginning of the number sequence so that the total number of digits is 4, enter the format as 0000. Click OK to apply.

After applying the custom format, you will see that the cells have been added with zeros at the beginning.

However, the actual data of those cells is not changed. If you select a cell with formatting, you will see that the original number is still displayed in the formula bar, for example, cell D3 shows 0011 but the formula bar displays 11. This also means that if you copy and paste the data as values to another cell, you will lose the leading zeros.

Method 4: Using the TEXT Function to Add Zeros in Excel

The TEXT function allows you to apply custom formatting to any numerical data in your spreadsheet.

= TEXT (value, format_text)

Where: value is the value or range of cells you want to format, format_text is the formatting to apply (usually in double quotes).

For example, if you want to create a 5-digit employee ID using the serial number column, enter the formula =TEXT(B3,”00000″) in cell D3 to apply to that cell, or =TEXT([@STT],”00000″) to apply to the entire column D.

Method 5: Using the RIGHT Function to Add Zeros in Excel

You can concatenate a string of zeros with a number and then remove the extra characters using the RIGHT function. The RIGHT function extracts the last N characters from a text value.

= RIGHT (text, [num_char])

Where: text is the text value you want to extract characters from, num_char is the number of characters to extract from the text. If this argument is not entered, only the first character will be extracted.

For example, = RIGHT(“000000” & B3,6) will concatenate a string of zeros with the first number in cell B3 and then return the last 6 characters with leading zeros.

Conclusion on How to Add Zeros in Excel

During data input and editing, you may find it annoying when Excel automatically removes leading zeros, but as you can see, there are many ways to preserve or add leading zeros in Excel. This article has covered 5 common and simple methods for you to apply.

Also, if you are interested in purchasing licensed office software, contact Buffcom.net for more information.