Most frustrating thing in excel is saving Account numbers, Phone numbers and ZIP codes, we end up losing the leading zeroes.
Today Let us see how to avoid it
What is leading zero?
Where excel leading zeroes are used?
How to use it with an example
What is leading zero?
A leading zero is any 0 digit that comes before a non-zero digit in a value.
Where excel leading zeroes are used?
In various situations of our life, we may need it .i.e Zip codes, Phone numbers, Bank accounts and many more…
How to use it with an example
It can be done in many ways, let’s look at a few of them…
3 Ways To Add Leading Zero To Excel And Maintain The Structure
1.Convert a number to text
2.Using REPT Function
3. Apply Custom formatting
1.Converting A Number To Text
The solution is to convert the cell format to “Text”
Step 1: Select the cell or the range of your choice and Click the number format icon
Step 2: Select the Text tab from the left display and click “Ok”
Step 3: Now you can just start off with the data entry.
@Checkout the example below
NOTE: As we are converting it to text, few calculations may not work.
2.Using REPT Function
REPT function in combination with LEN can help us achieve it
Copy paste the below formula where ever you need it by just changing the reference
Formula : =REPT(0,6-LEN(E1))&E1
The above formula makes sure that the cell always contains 6 digits in it, by adding the extra zeroes to it.
@Checkout the example below
Explained :
- REPT(0, No.of times), We have to provide the parameters like what to repeat and how many times to repeat it
- LEN(E1), Finds the length of E1 which is the cell we are performing the action on.
- &E1, It just adds the original text to the above calculation
NOTE: As we are converting it to text, few calculations may not work.
3. Apply Custom Formatting
The advantage with this way of adding leading zeroes is that you can perform the calculation on the data.
Step 1: Select the cell or the range of your choice and Click the number format icon
Step 2: Select the Custom tab from the left display
Step 3: Enter “000000” into the type field and click “Ok”
Step 4: Now you can just start off with the data entry.
@Checkout the example below
Note: Here only the formatting is changed but not the value, you can see the real value in the formula bar.
What are you waiting for, Give it a try buddy!!!
Hope the article has helped you…
