Whenever you try to add 0 in Excel before number, you will notice that Excel removes it automatically. This can inconvenience you, especially when you want your data to have leading zeros. However, Excel has steps that will help keep the zeros without deleting them, as shown by this article.
Use Format as Text Method
You will change the format of your range of data to text. Excel treats the data like texts instead of numbers and thus keeps the added zeros before the number. Do the following;
1. Highlight the numbers that you want to add zeros before
2. Open Home Tab
3. In the Format Section, Click on Numbers> On
4. Click on Text, then Okay
This changes the numbers to text format. The leading zeros you add will not be removed by Excel since the numbers are treated as texts.
Use custom format method to add 0 in Excel before number
Adding a custom format will format the numbers by adding zeros. This method does not change data to text. Follow the simple steps below.
1. Select and highlight the cells that you wish to add zeros before
2. Right-Click > Click Format Cells
3. Use the keyboard shortcuts Ctrl+ 1 alternatively.
4. Click on Numbers Tab
5. Choose Custom from the choices that appear
6. Go to the type box and add a custom format. For example, if you want all the digits, including the leading zeros, to be five, then this should be your custom format; 00000. If you have eight digits, then you need to add nine zeros. Use the sample feature to make sure the zero appears before your number.
7. Click OK
The data will not be changed once you choose the custom format. In case you copy and paste the numbers, the leading zeros will be left out.
Leading apostrophe method
You can add zero in Excel before number using the leading apostrophe method. Excel can enter a number as text when you use an apostrophe. The leading zeros in your values will be kept when you do this. This method is the most efficient and simplest by many people. Do the following;
1. Enter a character before any number in Excel
2. Excel assumes that the number you have typed is text and not number
3. Tap Enter
4. The leading zeros stay in your work and are not deleted
Use the Text Function method to add 0 in Excel before number
You can add 0 in Excel before number by using the text function method. This method allows you to use custom formatting for the numbers in your data. The function is; =TEXT( Value, Format)
Value- This is the numerical data you want to change to text so that you can apply the formatting method
Format- This is the format that you will apply to your numerical value. (You need to zeros greater than your number of digits by 1, Also make sure to enter them inside quotation marks)
You can use this formula to add leading zeros to your number.
Steps
1. Write numbers on one column, and then create a helper column on the right
2. Enter the formula in the first cell of the new column
3. Enter okay.
4. Apply the same formula to the rest of the cells. If they have equal digits, then drag or copy-paste the formula. But if the digits are not equal, you need to add the formatting zeroes accordingly.
Right Function method
The RIGHT function is a method that allows you to put zeros before the value whilst you use a formula. Using the function, you can remove any extra zeros before the number. The function is as follows; =RIGHT ( Text, Number)
Text- This represents the text that you want to remove characters from
Number- It is optional, and it represents the number of characters that you wish to extract from the text. In case you don’t enter this argument, you will only extract the first characters.
Steps
1. Create a helper column on the right
2. Type in the function in the first cell =RIGHT(“00″& A2,2) and press enter
3. Apply the function to the rest of the numbers according to the digits
Use the REPT function
REPT function is used to retain specific characters a number of times.
Syntax
=REPT(text, number_times)
Text: This is the text that you wish to retain
Number of times: The number of times you wish to retain the text
Steps
1. Create a helper column on the right column
2. Enter the formula on the first cell and press enter.
3. Select and drag down the formula to the rest of the cells
Use the Base function to add 0 in Excel before number
=Base() is an excel built-in function that converts numbers into text but with a given base.
Syntax
=BASE( Number,Base,[MinLength])
Number – the number you wish to add a leading 0
Base – Base you wish to convert the number to (In this example, we will use base 10 to add leading zeros)
MinLength – the number of digits you wish the converted value to have
Steps
1. Create a helper column on the right
2. Enter the formula in the first cell
3. Apply the formula to the rest of the cells. (Change the minimum length accordingly. If you have four digits, then use five as the minimum length)