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")
Solution 2
Using DEC2HEX and HEX2DEC functions
=DEC2HEX(HEX2DEC(A1),5)
Solution 3
Format cell as Number
Step 1
Right click cell and choose Format Cells… or press CTRL + 1
Step 2
Select Custom and in Type box enter 00000, click OK
