How to add/insert leading zeros to numbers or text in Excel?

[Origin]: https://www.extendoffice.com/documents/excel/792-excel-add-leading-zeros-to-number-text.html

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?

doc-add-leading-zeros1 -2 doc-add-leading-zeros2

 

 


arrow blue right bubble Add/ insert leading zeros to numbers with Text function

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.

doc-add-leading-zeros3

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.

doc-add-leading-zeros4

Tips: 1. As they are formulas, so when you need to copy and paste them to other places, you need to paste them as values.

2. This Text function can not add leading zeros for text.


arrow blue right bubble 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 Description: Selected cell with 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.

doc-add-leading-zeros5

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s