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

Where excel leading zeroes are used?
How to use it with an example

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.

##### 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â€¦