If statement limit workarounds

T. Louk · May 4th 2006

I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:

=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))

I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using. I'd appreciate any advise provided.
Thank you,
Terry

Wigi · May 4th 2006

Re: Formulas

Hi Terry

can you provide (the relevant part of) your worksheet?

Wigi

Badger101 · May 4th 2006

Re: Formulas

just from a quick look as you are virtually alwasy returning warning then you could reduce the number of Ifs by using the or statement,

for example

=if(or(a1=2,a1=5,etc...),"Warning","OK")

Fuzzy_Ng · May 5th 2006

Re: Formulas

You can define your own Excel Function to do this. You would not have the length problem. This is if you don't mind using VBA.

Alternatively, try to break you IF statement into several columns since they seem to be mutually exclusive.

If statement limit workarounds

Participate now!

Share