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?
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.
Step 2: Then press Enter key, and select cell C1 drag the 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.
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.
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.