special number format

Can someone help me with a special number format? I will have a range of cells and in that cells part numbers will be entered and formulas pull their info from these cells. The issue I am having is some of the part numbers start with actual numbers and those work fine, however a handful of part numbers start with 0. The 0 needs to stay there in order for the formulas to work. I tried to format as text but than formulas for some reason enter N/A. I can type the part numbers in as '01234 but I am trying to make it simple for other users so they don't have to type the '. Any suggestions?

You can either format it as text but then in formula use -- instead of just cell (for example --A1 instead of A1 where in A1 is number stored as text).

Other way would be to format numbers if you know specific size of numbers. In A1 write 123 right click-> Format cells -> Special -> 0000 will give you 0123

To format a cell itself to show a 0... just use Custom and enter 0's enough to represent the number of digits these numbers have..

to convert a number to a string with a leading zero in a formula, you can use the TEXT function... =TEXT(A1,"00000") will make 1234 into 01234. And this function can be embedded in another formula for manipulation.