The 4 Best Ways You Can Add Leading Zeros in Excel
Microsoft Excel’s default formatting system doesn’t let adding leading zeros. Therefore, when you need to work with bank account numbers, club membership IDs, ZIP codes, transaction IDs, etc., that start with zeros, you’re in big problem. Continue reading this tutorial to learn some simple and intuitive ways to add leading zeros in Excel.
How to Add Leading Zeros in Excel
1. Use an Apostrophe
The easiest and quick way of keeping the leading zeros in Excel is to use an apostrophe (`) before typing an account number, phone number, or ZIP code that starts with zeros.
Excel doesn’t omit the numbers automatically since placing an apostrophe before numbers convert the formatting to text.
After placing a leading zero, simply hit Enter. The zeros in the front will stay while the apostrophe won’t be visible. You can only find the apostrophe in the formula bar.
2. Formatting the Numbers
Excel omits the leading zeros only for those content that you enter as numbers. If the content in any cell is text, Excel won’t remove the zeros.
Thus, in this method, you’ll utilize this fact and convert your tables of numbers into text. You can also pre-format tables so that future entries also retain the zeros. Here’s what you need to do to add leading zeros in Excel:
- Type a column header like Club Membership ID.
- Select a cell range below the header. You can select as many as you want.
- Now, click on Home on the Excel ribbon.
- In the Number Format section, you’ll see General.
- Click on the drop-down menu and select Text.
- Now, enter any number that starts with zero, and Excel will keep the zeros.
3. Using REPT Function
Let’s say that you’ve copy-pasted a lot of order numbers to an Excel workbook. By default, Excel will omit any zeros in front of those numbers. If you know the number of leading zeros in those numbers, you can use the REPT function to add leading zeros. Here’s how:
- In this tutorial, we’re considering there is one zero before each member ID.
- So, the syntax of the formula will be as below:
=REPT(0,1)&B3
- Copy-paste the formula anywhere provided that cell B3 has any numerical digits.
- Excel will place a zero and link the number in cell B3 after that zero.
- Copy the same formula throughout the column to add leading zeros in Excel.
4. Apply Custom Format
You can also add leading zeros by creating a custom formula in the right-click context menu Format Cells. Here’s how you can do that:
- Select the range of membership IDs and hit Ctrl+1.
- Under the Number tab, choose Custom.
- Now, type zeros below the Type box.
- Let’s say the account number is six characters including one leading zero, so type six zeros.
- Click Ok.
- One leading zero will appear before each membership ID.
Conclusion
Now that you’ve learned fast and effective ways to add leading zeros in Excel, it shouldn’t be a challenge if you need to create tables of identification numbers that start with zeros. Also, you can increase the readability of your Excel tables by adding leading zeros so that all the numbers are of identical length.
Did you know that Excel also lets you send emails to the masses? Read on, and you can also send bulk email from Outlook using Excel VBA.
