Thank you both for responding -- I realize that this statement was reasonably
complex, and definitely wasn't the most readily intuitive, but that's the
main reason why I turned here for help when I found myself stuck.

With that said, I made sure to use both the Evaluate Formula tool and the
statement deconstruction technique to ensure that each part of the formula
works exactly as I intended it (which, thankfully, it does, with the sole
exception of the "and/or" combination). Unfortunately, while I was able to
use the two resolution techniques to confirm my suspicions as to where the
problem lied, neither was actually able to help pin-point the exact nature of
the problem or how to fix it.

Luckily, after taking the time to think about it some more over the holiday,
I was finally able to see that the statement was doing exactly what it
supposed to do, based on the way I wrote it -- the problem wasn't in the
statement, but rather, in my logic (i.e., the "OR" statement shouldn't exist
at all, but rather be included as additional terms in the "AND" statement).

Specifically, if the number of characters in the cell are greater than five
and less than nine, and the exact character string in the cell is not equal
to "CANADA" <b<iand</i</b (rather than "OR") not equal to "FOREIGN", then
the formula should pre-fix the characters (in this case, the only character
strings that are left over are comprised of numeric digits) with as many
zeroes as necessary to set the number of characters in the cell equal to 9
(dashes do not count, as I had previously removed them all).The reason this
works is because I want all cases of the character strings other than the
text strings to be appended with leading zeroes.

Now I realize that what I had written before caused the "OR" statement to
trigger the "REPT" function unintentionally, because every time it was true
for one of the text phrases (e.g., "CANADA"), it had to be false for the
other, and yet nonetheless, given the nature of the "OR" statement, it was
returning a total "True" value, which the preceding "AND" statement used to
initiate the "REPT" trigger.

In any case, thank you both, again, for your time and your suggestions -- I
definitely appreciate it, and couldn't have understood the problem without
them.

-- A Need to Excel

PS: Being that there were only two text exceptions in this column, I didn't
mind writing them into the formula, but in the interest of maintaining
manageability and scalability, do either of you (or anyone else for that
matter), know of a function in excel which will dictate the type of
characters used in that cell? Specifically, is there anyway that I could
write something such as "If the characters of the cell are only text, then
do..., otherwise, if the characters of the cell are only numeric, then
do..."? I'd also be curious to know if there is a function which identifies
the contents of a cell as alphanumeric. Thanks.


"joeu2004" wrote:

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(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 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))