22,784
Like
Share
Report
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 was recently working with a client who was preparing data for import. One of the issues they encountered when cleansing the data was that Excel stripped the leading zeros from the ID fields and were looking for a way to add them back in order to import the file with the required ID format.
They were looking for a formula to do this and came up with an approach of prepending 0s and using the RIGHT function, but there is a claaner approach using the TEXT function.
The TEXT function is very flexible and one I’ve used before when formatting numbers to two decimal places.
The ID numbers were a variety of lengths, but needed to be formatted to six characters long with zero prefixes.
This can be simply done using the TEXT function as shown here:
=TEXT($A52,"000000")
Click to show/hide the Excel Snippets Series Index
Read original post Excel Snippets: Pad Numbers with Leading Zeroes at azurecurve|Ramblings of an IT Professional
*This post is locked for comments