Excel automatically removes the leading zeros from a number when entered in a cell, making it impossible to write 001. Below, learn 11 possible ways be able to do this.
Method 1 – Write 001 in Excel with Custom Number Format
If you want to enter numbers with leading zeroes and store them as numbers, follow the steps below.
Steps:
- Select the cell ranges (B4:B5) where you will store your data.
- Open the Format Cells dialog box by pressing CTRL+1.
- Select the Custom category from the Number tab. Enter 00# in the Type: box. Hit the OK button.
- You can use more zeros before # if you want. The number will change accordingly.
- Enter 001 in cell B4. You will see that the formula box is showing 1 but the cell is showing 001 as desired.
- Now try this in cell B5 by entering 002. You will always get the desired result.
Method 2 – Write 001 Using the Apostrophe (‘) Symbol
Steps:
- Type an apostrophe (‘) before tying the number.
- Excel will consider the number as text. The number will be stored as entered. (An error symbol may show up for entering the number as text.)
Method 3 – Write 001 Using the Ampersand (&) Symbol
Steps:
- Use the following formula in cell B4 to get the desired result.
="00"&1
Method 4 – Write 001 Using Excel TEXT Function
Steps:
- You can enter the following formula in cell B4. The TEXT function in the formula converts the number to the desired text format as shown below.
=TEXT(1,"000")
Method 5 – Write 001 Using the TEXTJOIN Function
Steps:
- Use the following formula in cell B4 to write 001. The formula contains the TEXTJOIN function.
=TEXTJOIN("",,"00",1)
Read More: How to Write 00 in Excel
Method 6 – Write 001 Using the CONCAT Function in Excel
Steps:
- Use the CONCAT function. Enter the following formula in cell B4. Then you will get the desired result as follows.
=CONCAT("00",1)
Method 7 – Write 001 Using the CONCATENATE Function
Steps:
- Using the CONCATENATE function gives the same result.
=CONCATENATE("00",1)
Method 8 – Write 001 in Excel Using the RIGHT Function
Steps:
=RIGHT("00"&1,3)
Method 9 – Write 001 in Excel Using the BASE Function
Steps:
- Use the BASE function to write 001 in Excel. Enter the following formula in cell B4. You will get the result as shown below.
=BASE(1,10,3)
Read More: How to Add Leading Zeros in Excel
Method 10 – Get 001 Using the REPT and LEN Functions
Steps:
=REPT(0,3-LEN(1))&1- Here, 0 is repeated 3-LEN(1) times. 3 stands for the length of the output text. LEN(1) gives the length of 1 which is 1. Therefore, 0 is repeated 3-1=2 times and concatenated with 1 by the ampersand (&) symbol.
Method 11 – Write 001 Using the Text Format in Excel
Steps
- Select the entire range where you will store your data. In this case, the range is B4:B5.
- Press CTRL+1. This will open the Format Cells dialog box.
- Select the Text category from the Number tab. Hit the OK button.
- Now enter 001 in cell B4. It will be stored as such.
- Enter 002 in cell B5 and see the following result.
- You can ignore the Error. It is because you are storing numbers as texts. To remove it, select the cells first. Click on the error sign. Choose Ignore Error. The error sign will no longer be visible.
Read More: How to Add Leading Zeros in Excel Text Format
Things to Remember
- Don’t use the number pad on your keyboard to press CTRL+1 to open the Format Cells dialog box.
- Make sure you’ve used the double quotes (“”) properly in the formulas.
- You can use cell reference instead of typing 1 in the formula if you want to apply the formulas to the existing dataset.
- You may face some real problems if you store numbers as text. Because you won’t be able to sort or filter them as numbers if needed.
Download Practice Workbook
You can download the practice workbook from the download button below.
Related Articles
<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
