On Jul 3, 9:52 am, A Need to Excel
<A Need to wrote:
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.
[....]
Formula is as follows:
=IF(LEN(TRIM(CLEAN(K150)))=0,"",(IF(LEN(TRIM(CLEAN (K150)))=5,TRIM
(CLEAN(K15*0))&"0000",IF(AND(9(LEN(TRIM(CLEAN(K15 0))))5,(OR(TRIM
(CLEAN(K150))<"CANA*DA",TRIM(CLEAN(K150))<"FOREI GN"))),
(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))*&TRIM(CLEAN(K150))),
TRIM(CLEAN(K150))))))

I am not taking the time to parse and understand the above, and the
following might not address your specific question. But I do notice
one glaring syntax error that will be troublesome at least in __some__
cases. You wrote:

AND(9(LEN(TRIM(CLEAN(K150))))5,
(OR(TRIM(CLEAN(K150))<"CANA*DA",
TRIM(CLEAN(K150))<"FOREIGN")))

"9LEN(...)5" does not do what you intended. I think you want:

AND(9LEN(...), LEN(...)5, OR(TRIM(...)<"CANADA",
TRIM(...)<"FOREIGN")

PS: I don't know why you bother to check the length. It seems
sufficient just to compare with the expected text, "CANADA" and
"FOREIGN".

FYI, I think it would be easier for you (and everyone else) to read
and maintain if you eliminated unnecessary parentheses. For example,

(REPT("0",(9-(LEN(TRIM(CLEAN(K150))))))&TRIM(CLEAN(K150)))

can be written more simply as:

REPT("0", 9-LEN(TRIM(CLEAN(K150)))) & TRIM(CLEAN(K150))