About Us

Bill Ridgeway

"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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can I include a "validation drop down" in a conditional statement?	JanW	Excel Worksheet Functions	1	June 5th 07 08:50 PM
"An unexpected error has occured"	Froshawn	Excel Discussion (Misc queries)	0	April 16th 07 07:32 AM
embedding "ISERROR" function into an "IF" statement	[email protected]	Excel Worksheet Functions	8	January 4th 07 12:01 AM
"Reading"the logic behind too-long command statements	Alex	Excel Worksheet Functions	2	August 29th 06 08:12 PM
inserting a conditional "go to" command on a excel "if" function	velasques	Excel Worksheet Functions	5	March 10th 06 08:16 PM

Menu

About Us