How to add leading zero to fixed number length in Excel?

You have a list of numbers with varying character lengths, and it seems cluttered and disorganised. You may add a leading zero to any fixed numbers that have the same length as the screenshot shown below to make the display more organised and clearer.

Using TEXT Function

You will be able to add a custom formatting to any numerical data that is already present in your spreadsheet by using the TEXT function.

= TEXT ( Value, Format) 

This is the value that you wish to convert to text and add formatting to once it has been converted.

Format − This is the formatting that should be applied to the document.

Step 1

It's possible that you'd want your data collection to have a uniform appearance, as seen below −

Step 2

You may use the formula that is provided below in order to add zeros to a number that is currently located in cell B2 in order to get the total number of digits up to 6.

=TEXT(A2,"000000")

Step 3

When you hit the Enter button on your keyboard, the result is shown below.

Step 4

After that, you need to repeat the number pattern by dragging the autofill indicator lower.

Step 5

After that, pull down the autofill until the desired result is shown below.

Step 6

A number with six digits may be generated using this easy method by adding the appropriate number of zeros to the beginning of the value. It is essential to take into account the fact that the formula returns a text value. Although it seems to be a number, the underlying data type is really text.

Conclusion

This method could only be used with a dataset consisting of numbers. In the event that you have employee ids such as A1, A2, A3, and so on, then they are text, and they will not change even if you apply the custom format that was shown before.

An advantage of turning data into text is that it enables you to use it in lookup formulae, such as VLOOKUP and INDEX/MATCH, which allow you to get the information of an employee by using the employee id of that person.