Maximum Length

Posted by Andy Gee on January 18, 2002 1:47 AM

Anyone know what the maximum number of IF opperators you can get in a cell? Or how the rules work? or if there's an easier way to:

A15:
=IF(ISERR(IF(FIND(" ",B15)=1,1,0)),B15,IF(FIND(" ",B15)=1,RIGHT(B15,LEN(B15)-1),B15))

B15:
=IF(IF(ISERR(FIND("Track",C15)),0,FIND("Track",C15))=1,IF(IF(ISERR(FIND("Track",C15)),0,FIND("Track",C15))>0,RIGHT(C15,LEN(C15)-LEN(LEFT(C15,8))),0),IF(ISERROR(TRIM(RIGHT(Tracks!A15,LEN(Tracks!A15)-FIND(".",Tracks!A15)))),TRIM(RIGHT(CONCATENATE(".",Tracks!A15),LEN(CONCATENATE(".",Tracks!A15))-FIND(".",CONCATENATE(".",Tracks!A15)))),TRIM(RIGHT(Tracks!A15,LEN(Tracks!A15)-FIND(".",Tracks!A15)))))

C15:
=IF(ISERROR(TRIM(RIGHT(Tracks!A15,LEN(Tracks!A15)-FIND(".",Tracks!A15)))),TRIM(RIGHT(CONCATENATE(".",Tracks!A15),LEN(CONCATENATE(".",Tracks!A15))-FIND(".",CONCATENATE(".",Tracks!A15)))),TRIM(RIGHT(Tracks!A15,LEN(Tracks!A15)-FIND(".",Tracks!A15))))

The data in Tracks!A15 can be anything like...
6. Hey You
7. Marooned i'm marooned
8. i'm The Great Gig in the Sky
9. Set the Controls for the Heart of the Sun
10. Money
Track 1 Keep Talking
...and it needs to be trimmed to the name only!

Any help on the max length or formula would be very much appreciated.

Posted by Robin on January 18, 2002 2:31 AM

Posted by Aladin Akyurek on January 18, 2002 3:46 AM

Andy --

It seems you're looking for "Track" in Tracks!A15.

Why not use something like:

=IF(ISNUMBER(SEARCH("Track",Tracks!A15)),"Track","")

PS. Seven nested IFs is the max. However, it's better to keep the number of nested IFs small, at most four. Anything beyond makes it hard to process and debug. In most cases, nested IFs boil down to a judiciously designed VLOOKUP formula.

Aladin

========

Posted by Andy Gee on January 18, 2002 11:33 AM

Thanks for the help I'll try a vlookup formula!