How to Write 001 in Excel (11 Effective Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel automatically removes the leading zeros from a number when you enter it in a  cell. Therefore, you cannot directly write 001 in Excel and display it as such. This article shows 11 possible ways to write 001 in Excel. The following picture highlights the purpose of this article.

How to Write 001 in Excel


How to Write 001 in Excel: 11 Methods

Anytime you try to enter a number with zeros before them, excel automatically removes the leading zeros as shown in the following picture.

This becomes problematic when you need to enter zip codes, telephone numbers, social security numbers, etc. which may contain zeros at the beginning. But there are ways you can write 001 in Excel both as a text and a number. The following 11 methods I’m going to illustrate for you will help you to do that.


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:

  • First, select the cell ranges (B4:B5) where you will store your data.

  • Then, open the Format Cells dialog box by pressing CTRL+1.
  • After that, select the Custom category from the Number tab. After that, enter 00# in the Type: box. Then hit the OK button.
  • You can use more zeros before # if you want. Then the number will change accordingly.

Write 001 in Excel using Custom Number Format

  • Now, enter 001 in cell B4. Then you will see that the formula box is showing just 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.


2. Write 001 Using the Apostrophe (‘) Symbol

First of all, you can just type an apostrophe () before tying the number. Then, Excel will consider the number as text. The number will be stored as entered as follows. An error sign may show up for entering the number as text.

Write 001 in Excel using Apostrophe Symbol


3. Write 001 Using the Ampersand (&) Symbol

You can also use the ampersand (&) symbol to write 001 in Excel.

Steps:

  • Use the following formula in cell B4 to get the desired result.
="00"&1

Write 001 in Excel using the Ampersand Symbol


4. Write 001 Using Excel TEXT Function

Alternatively, 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")

Write 001 in Excel Using the TEXT Function


5. Write 001 Using the TEXTJOIN Function

  • Now, use the following formula in cell B4 to write 001. The formula contains the TEXTJOIN function.
=TEXTJOIN("",,"00",1)

Write 001 in Excel Using the TEXTJOIN Function

Read More: How to Write 00 in Excel


6. Write 001 Using the CONCAT Function in Excel

  • Another alternative to writing 001 is by using the CONCAT function. Enter the following formula in cell B4. Then you will get the desired result as follows.
=CONCAT("00",1)

Write 001 in Excel Using the CONCAT Function


7. Write 001 Using the CONCATENATE Function

=CONCATENATE("00",1)

Write 001 in Excel Using the CONCATENATE Function


8. Write 001 in Excel Using the RIGHT Function

  • Enter the following formula with the RIGHT function in cell B4 to get the same result.
=RIGHT("00"&1,3)

Write 001 in Excel Using the RIGHT Function


9. Write 001 in Excel Using the BASE Function

  • You can use the BASE function to write 001 in Excel. To do that, enter the following formula in cell B4. After that, you will get the result as shown below.
=BASE(1,10,3)

Write 001 in Excel Using the BASE Function

Read More: How to Add Leading Zeros in Excel


10. Get 001 Using the REPT and LEN Functions

  • To get 001 in Excel using REPT and LEN functions, you need to apply the following formula in cell B4.
=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.

Write 001 in Excel Using the REPT and LEN Functions


11. Write 001 Using the Text Format in Excel

You can directly enter 001 or 0002 and so on in Excel by using Text Format in the desired cell range. You need to follow the steps below to learn how to do that.

📌 Steps

  • First, select the entire range where you will store your data. In this case, the range is B4:B5.
  • Now, press CTRL+1. This will open the Format Cells dialog box. After that, select the Text category from the Number tab. Next, hit the OK button.

  • Now enter 001 in cell B4. It will be stored as such.

  • After that, enter 002 in cell B5 and see the following result.

Write 001 in Excel Using TEXT Format

  • You can just ignore the Error It is because you are storing numbers as texts. To remove it, select the cells first. Then click on the error sign. Next, choose Ignore Error. Then 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.


Conclusion

Now you know how to write 001 in Excel. Hope this article has helped you with your problem. Please use the comment section below for further queries or suggestions. You can also visit our blog to learn more about Excel.


Related Articles


<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo