# If statement limit workarounds

• 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

• Re: Formulas

Hi Terry

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

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• 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")

• 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!