Nested levels of 61 giving error as over 64 levels

There are instructions at the top of the page explaining how to attach your sample workbook.

The yellow banner at the top contains instructions telling you how to upload your workbook.

Provide a desensitised copy.

Nobody has the time to imagine the data and recreate it in order to test whether it's Excel or you.

Having looked at the formula, I can see other nesting going on with AND(OR(...,...), so I don't actually think it's Excel.

Your formula can perfectly be shortened
Creating a small list in K1:K5 (with "one" to "five") replaces

Formula:

Please Login or Register  to view this content.

with only

Formula:

Please Login or Register  to view this content.

by extending the list the formula does not change except for the range and the number of rows
So if you have to lookup 100 words just build a list with the one hundred words and enter

Formula:

Please Login or Register  to view this content.

( I didn't bother removing the semicolons as it is only an example)
Replace k1:K100 with whatever suits best

First,
Although it seems be 61 IF's level, but i believe Excel counts IF's like this:
IF(AND(statement_1, statement_2)...
equals: IF(statement_1,IF(statement_2,...)
then more IF's are counted.
Second,
you are using un-needed AND,OR which can be delete, like this:

IF(OR(ISNUMBER(SEARCH("32",E2)),ISNUMBER(SEARCH("32x",E2)),"32",...
in which first OR is enough:
IF(ISNUMBER(SEARCH("32",E2)),"32"...

and similar with 35,39,52,59,60,61...

and an AND in "26"
IF(AND(ISNUMBER(SEARCH("26",E2)),ISNUMBER(VALUE(MID(E2,SEARCH("26",E2)+4,5)))),"26",...
can be:
IF(ISNUMBER(VALUE(MID(E2,SEARCH("26",E2)+4,5))),"26",...

And below is working with 61 IF's:
=IF(ISNUMBER(SEARCH("One",E2)),"One",
IF(ISNUMBER(SEARCH("Two",E2)),"Two",
IF(ISNUMBER(SEARCH("Three",E2)),"Three",
IF(ISNUMBER(SEARCH("Four",E2)),"Four",
IF(ISNUMBER(SEARCH("Five",E2)),"Five",
IF(ISNUMBER(SEARCH("SIX",E2)),"Six",
IF(ISNUMBER(SEARCH("Seven",E2)),"Seven",
IF(ISNUMBER(SEARCH("Eight",E2)),"Eigt",
IF(ISNUMBER(SEARCH("Nine",E2)),MID(E2,22,LEN(E2)-22),
IF(ISNUMBER(SEARCH("Ten",E2)),"Ten",
IF(ISNUMBER(SEARCH("11",E2)),"11",
IF(OR(ISNUMBER(SEARCH("12",E2)),ISNUMBER(SEARCH("CHECK #",E2)),AND(LEFT(E2,5)="CHECK",NOT(ISNUMBER(SEARCH("Print",E2))))),"12",
IF(ISNUMBER(SEARCH("13",E2)),"13",
IF(ISNUMBER(SEARCH("14",E2)),"14",
IF(ISNUMBER(SEARCH("15",E2)),"15",
IF(ISNUMBER(SEARCH("16",E2)),"16",
IF(ISNUMBER(SEARCH("17",E2)),"17",
IF(ISNUMBER(SEARCH("18",E2)),"18",
IF(ISNUMBER(SEARCH("19",E2)),"19",
IF(ISNUMBER(SEARCH("20",E2)),"20",
IF(ISNUMBER(SEARCH("21",E2)),"21",
IF(ISNUMBER(SEARCH("22",E2)),"22",
IF(ISNUMBER(SEARCH("23",E2)),"23",
IF(ISNUMBER(SEARCH("24",E2)),"24",
IF(ISNUMBER(SEARCH("25",E2)),"25",
IF(ISNUMBER(VALUE(MID(E2,SEARCH("26",E2)+4,5))),MID(E2,SEARCH("26",E2)+10,25),
IF(ISNUMBER(SEARCH("27",E2)),"27",
IF(ISNUMBER(SEARCH("28",E2)),"28",
IF(ISNUMBER(SEARCH("29",E2)),"29",
IF(ISNUMBER(SEARCH("30",E2)),"30",
IF(ISNUMBER(SEARCH("31",E2)),"31",
IF(ISNUMBER(SEARCH("32",E2)),"32",
IF(ISNUMBER(SEARCH("33",E2)),"33",
IF(ISNUMBER(SEARCH("34",E2)),"34",
IF(ISNUMBER(SEARCH("35",E2)),"35",
IF(ISNUMBER(SEARCH("36",E2)),"36",
IF(ISNUMBER(SEARCH("37",E2)),"37",
IF(ISNUMBER(SEARCH("38",E2)),"38",
IF(ISNUMBER(SEARCH("39",E2)),"39",
IF(ISNUMBER(SEARCH("40",E2)),"40",
IF(ISNUMBER(SEARCH("41",E2)),"41",
IF(ISNUMBER(SEARCH("42",E2)),"42",
IF(ISNUMBER(SEARCH("43",E2)),"43",
IF(ISNUMBER(SEARCH("44",E2)),"44",
IF(ISNUMBER(SEARCH("45",E2)),"45",
IF(ISNUMBER(SEARCH("46",E2)),"46",
IF(ISNUMBER(SEARCH("47",E2)),"47",
IF(ISNUMBER(SEARCH("48",E2)),"48",
IF(ISNUMBER(SEARCH("49",E2)),"49",
IF(ISNUMBER(SEARCH("50",E2)),"50",
IF(ISNUMBER(SEARCH("51",E2)),"51",
IF(ISNUMBER(SEARCH("52",E2)),"52",
IF(ISNUMBER(SEARCH("53",E2)),"53",
IF(OR(ISNUMBER(SEARCH("54x",E2)),ISNUMBER(SEARCH("54w",E2))),"54",
IF(ISNUMBER(SEARCH("55x",E2)),"55",
IF(ISNUMBER(SEARCH("56",E2)),"56",
IF(ISNUMBER(SEARCH("57",E2)),"57",
IF(ISNUMBER(SEARCH("58",E2)),"58",
IF(ISNUMBER(SEARCH("59",E2)),"59",
IF(ISNUMBER(SEARCH("60",E2)),"60",
IF(ISNUMBER(VALUE(MID(E2,SEARCH("61",E2)+15,5))),MID(E2,SEARCH("61",E2)+20,LEN(E2)-29),"XXX")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

A shorter version, I believe it works:

Please Login or Register  to view this content.

Hi bebo

As far as I am aware, the whole point of using nested IF formulas is to return the first result where a specified condition is true.
So your short-formula doesn't do what RJK's long-formula does.
If cell [E2] contains "qprj1000zz1Four26Two29xxx17" then RJK's formula will give a result of "Four"

If RJK used a custom function, you could have a lot more than 64 condition levels.
So you could replace that huge formula with say, =zzz(E2)

zeddy

Im not going to try and wade through that monster (kudos on constructing it though), but if excel says you have exceeded the limit of 64, then that's what you have done, even though it may not seem that you have. Perhaps there is a branch in there that is in error or you did not notice, that is taking you down a route that ends up with 64 IF's?

Either way, it has been my experience that, while beasts like this really do look impressive, there is almost always a far simpler and shorter way to get to the same result, often with much easier to understand and follow syntax's. Below is 1 way that you could find what your cell contains, and then return the number (assuming your example here is representative of your RL situation. It uses a lookup table that contains your "numbers", which then used against your cell to determine what it contains...

	N	O	P
2	one	abconedef	one
3	two	abcfourefg	four
4	three
5	four
6	five
7	six

The cells on col O would contain your text cell.
P2=LOOKUP(9.99999999999999E+307,SEARCH($N$2:$N$7,O2),$N$2:$N$7)
I copied it down so you can see it working against other text

Originally Posted by RJK

Thank you both bebo & eddy, I'm not an excel expert, but I don't think using and/or counts as levels.

I pointed this out to you back in post #9 ...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.