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
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.