This is one of the finest forumlae I’ve ever seen. Check this post at MrExcel.
If you want to have a value specific number of digits otherwise add zeros in front of it, this formula could be used:
where n is the number of digits you want.
So 4555 becomes 000004555 with formula =TEXT(4555,REPT(“0”,9))