"A Need to Excel" <A Need to wrote in
message ...
Hi,

The worksheet I'm working on now has been pulled together from a number of
different sources (each with their own issues) into what is supposed to be
a
comprehensive mailing list.

I wrote the statement below to try to deal with some of that inconsistency
in the Zip Code field -- some entries have five digits, some nine, while
others are either incomplete or entered as text. I formatted the column
so
with the "Special" Zip format, but I need to account for leading zeroes,
and/or truncated (5-digit) Zips.

For the most part this formula works, but for some reason, I'm getting
leading zeroes appended to the text entries (CANADA/FOREIGN) in addition
to
the numeric ones. Does anyone know why, and/or have a suggestion on how
to
adapt this formula to do what I'd like it to do? Any and all assistance
would be greatly appreciated. Thanks.

Formula is as follows:

=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN (K150)))=5,TRIM(CLEAN(K150))&"0000",IF(AND(9(LEN( TRIM(CLEAN(K150))))5,(OR(TRIM(CLEAN(K150))<"CANA DA",TRIM(CLEAN(K150))<"FOREIGN"))),(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150))),TRIM (CLEAN(K150))))))

I'm not going to try to analyse that one. A solution I've found to work
with enigmatic formula is to break out each of the IF statements into
separate lines (several if an AND or OR is involved) and, with test data,
check the actual outcome with the expected outcome. This is sometimes the
way I construct complicated formula.

Hope this helps!

Bill Ridgeway
Computer Solutions

On Jul 3, 10:56 am, "Bill Ridgeway" wrote:
I'm not going to try to analyse that one. A solution I've found to work
with enigmatic formula is to break out each of the IF statements into
separate lines

Agreed. I have also had some success with the Excel Evaluate Formula
tool. Select the cell, then click on Tools Formula Auditing
Evaluate Formula.

(But sometimes EF does not reveal everything we need to know, and
Bill's suggestion is the better alternative.)