Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)


To totally unlock this section you need to Log-in


Login

Supposing you need to input some zip codes or phone numbers in cells of Excel, then you will find Microsoft Excel removes the leading zeros by default. This article is going to show you two methods about how to add leading zeros to numbers or text in ranges of cells in Excel.

Supposing you have a range of data, and you need to add zeros in front of each cells as following screenshots shown. How could you quickly add the zeros before the numbers?

Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)

The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length.

Step 1: Enter the formula =Text(A1, "00000") in a blank cell which is adjacent to the data cell.

Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)

Step 2: Then press Enter key, and select cell C1 drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. Then you will view all the numbers in A1:B6 are copied and pasted to C1:D6 with leading zeros. And each numbers contains 5 digits.

Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)

As they are formulas, so when you need to copy and paste them to other places, you need to paste them as values. This Text function can not add leading zeros for text.

The following are other examples:

Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)

Add/ Insert Leading Zeros To Numbers And Text With Concatenate Function

If you want to insert specific digit of leading zeros into each number, for example three leading zeros for each number, you should try the Concatenate function.

Step 1: Enter the formula =Concatenate("000", A1) in a blank cell and press Enter key. In this case, we enter the formula in Cell C1.

Step 2: Click the Cell C1, and drag the fill handle across the range that you want to fill. Then you will view numbers in A1:B6 are pated to C1:D6, and each number contains three leading zeros.

Add/Insert Leading Zeros To Numbers Or Text (Microsoft Excel)

Credit card numbers are rounded down

Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero. In the case of number codes that are 16 digits or larger, you must use a text format. To do this, you can do one of two things:

Format the column as Text

Select your data range and press Ctrl+1 to launch the Format > Cells dialog. On the Number tab, click Text.

Note: This will not change numbers that have already been entered. It will only affect numbers that are entered after the format is applied.

Use the apostrophe character

You can type an apostrophe (') in front of the number, and Excel will treat it as text.