View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default Adding a 0 to a four digit number

As these are codes then they should be stored as text, so the following
will do what you want:

=IF(LEN(A1)=5,""&A1,IF(LEN(A1)=4,"0"&A1,"00"&A1))

This caters for 3-, 4- and 5-digit numbers, but hopefully you can see
the logic of how to cope if you also have 2 digit numbers. It does not
check for 6-digit or larger numbers.

Hope this helps.

Pete