I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
A colleague posted an Excel formula which could be used to zero pad a column in Excel, which was to be used as the ID column for data being integrated to Microsoft Dynamics 365 Business Central. The formula they had used was longer and more convoluted than was needed.
In fact you only need to use one function to zero pad. That function is TEXT which you provide with two parameters; the cell you want to zero pad and a string of zeros, the length of the output required.
So, to zero pad cell A2 with 6 zeros we would use the following:
=TEXT($A2, "000000")
You can also use the REPT function to provide the required zero [adding length, which if you’re dealing with a long string can ease readability).
The REPT function takes two parameters; the first is the character to repeat and the second is the number of times to repeat:
=TEXT($A2 ,REPT("0", 6))
Click to show/hide the Excel Snippets Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.