Convert number to text formula stopped working???

[reneev]

Using Excel 365 desktop but also tried in the web version too.

I use the Text function to convert a number to text in order to add leading zeros to a number. For example, our Employee ID's are five characters with a leading zero. I use this function to convert an entire column of 4 digits to 5 with a leading zero i.e. 5449 to 05449.

Entering this formula should result in 05449

However the result removes all but one zero and results 5449.

I've been using this function almost daily for YEARS, and all of a sudden it's not working???

 

[Joe4]

If you want 5 digits, you should use:

=TEXT(A1,"00000")

If you use a formula with only one zero, the length of the result returned will never be more than the greater of:
- one
- the length of your original string

 

[reneev]

OMG, thank you. Apparently I'm not awake because I was completely forgetting the "quotes" around the zeros.

 

[Joe4]

You are welcome.
Glad I was able to help!