Home  »  Excel   »   3 Ways of Adding Leading Zeroes to Cell Values in Excel

3 Ways of Adding Leading Zeroes to Cell Values in Excel

By | January 14, 2017

Introduction

When you paste a data into Excel, it looks for numbers – when found then it formats it by removing leading zeros.

If you want to keep the zeros set the type of data in cells to text before pasting.
When you already have values in Excel and you want to put leading zeros you have 3 ways of doing it.

Scenario

There are fields that should have five characters with lead zeros padding out the string.

“32” should become “00032”,
“321” should become “00321”, and
“32198” should remain “32198”

Solution 1

Using TEXT function

=TEXT(A1,"00000")

Adding leading zeros in Excel by TEXT function
image-3118

Adding leading zeros in Excel by TEXT function

Solution 2

Using DEC2HEX and HEX2DEC functions

=DEC2HEX(HEX2DEC(A1),5)

Adding leading zeros in Excel by DEC2HEX and HEX2DEC functions
image-3119

Adding leading zeros in Excel by DEC2HEX and HEX2DEC functions

Solution 3

Format cell as Number

Step 1

Right click cell and choose Format Cells… or press CTRL + 1

Cell context menu in Excel
image-3120

Cell context menu in Excel

Step 2

Select Custom and in Type box enter 00000, click OK

Format cell window in Excel
image-3121

Format cell window in Excel

Leave a Reply

Your email address will not be published.