Finky said:
The problem with this is many zip codes begin with zeros, and, with a
varying
amount of digits I couldn't force the zeros, but I appreciate your help.
It sounds to me like you are treating some zip codes as numbers (those
without dashes) and some zip codes as text (those with dashes).
First, let me say that I think that is a bad idea in principle. More about
that below.
But if my assumption is correct, I think the following should work in both
cases:
=if(len(A1)<=5, text(A1,"00000"), left(A1,5))
Note that the result is always text.
Since some zip codes __must__ be treated as text (those with dashes),
__all__ zip codes should be treated as text. It is a good idea for cell
values to be homogenous. This makes it easier to manipulate them in other
formulas, as you see here.
(One exception: the null string ("") should be permissible in cells that
normally have numbers. Ideally, Excel would treat the null string as zero
in numeric expression, just as it treats empty cells. But Excel does not
<sigh>.)
If you are importing the data, usually you can tell the Import Wizard to
treat the column with zip codes as text.
If you are entering the data manually, prefix the zip code with a single
quote (aka apostrophe). Alternatively, set the cell format to Text before
entering data.
----- original message -----