Is there a maximum number of FIND functions?

M

ManosS

I am using this function successfully

=IF(NOT(ISERROR(FIND(" FLR",AC11))),FIND("
FLR",AC11),IF(NOT(ISERROR(FIND("FLOOR",AC11))),FIND("FLOOR",AC11),
IF(NOT(ISERROR(FIND(" RM ",AC11))),FIND(" RM ",AC11),
IF(NOT(ISERROR(FIND(" ROOM ",AC11))),FIND(" ROOM ",AC11),
IF(NOT(ISERROR(FIND("SUITE",AC11))),FIND("SUITE",AC11))))))

However, if I add another row, Excel tells me I have an error and
highlights the very next FIND

IF(NOT(ISERROR(FIND("BLDG",AC11))),FIND("BLDG",AC11)

I did not think this was exceeding the limit on 7 nested functions. Can
someone educate me?
Thank you

Also - I cannot seem to get this FIND to work to locate occurences of
#. Any suggestions on that are welcome.

IF(NOT(ISERROR(FIND("#",AC1115))),FIND("#",AC1115)," ")
 
H

Harlan Grove

ManosS wrote...
I am using this function successfully
Click to expand...

What follows is a 'formula' calling many 'functions'.
=IF(NOT(ISERROR(FIND(" FLR",AC11))),FIND("FLR",AC11),
IF(NOT(ISERROR(FIND("FLOOR",AC11))),FIND("FLOOR",AC11),
IF(NOT(ISERROR(FIND(" RM ",AC11))),FIND(" RM ",AC11),
IF(NOT(ISERROR(FIND(" ROOM ",AC11))),FIND(" ROOM ",AC11),
IF(NOT(ISERROR(FIND("SUITE",AC11))),FIND("SUITE",AC11))))))

However, if I add another row, Excel tells me I have an error and
highlights the very next FIND

IF(NOT(ISERROR(FIND("BLDG",AC11))),FIND("BLDG",AC11)

I did not think this was exceeding the limit on 7 nested functions. Can
someone educate me?
Click to expand...
....

Reformat your formula using indentation to show call level.

=IF(
NOT(
ISERROR(
FIND(" FLR",AC11)
)
),
FIND("FLR",AC11),
IF(
NOT(
ISERROR(
FIND("FLOOR",AC11)
)
),
FIND("FLOOR",AC11),
IF(
NOT(
ISERROR(
FIND(" RM ",AC11)
)
),
FIND(" RM ",AC11),
IF(
NOT(
ISERROR(
FIND(" ROOM ",AC11)
)
),
FIND(" ROOM ",AC11),
IF(
NOT(
ISERROR(
FIND("SUITE",AC11) <- here's the deepest call level, 7
nested levels
)
),
FIND("SUITE",AC11)
)
)
)
)
)

If you add another IF call at the same level as the final FIND call
immediately above, it'd look like the following with indentation.

=IF(
NOT(
ISERROR(
FIND(" FLR",AC11)
)
),
FIND("FLR",AC11),
IF(
NOT(
ISERROR(
FIND("FLOOR",AC11)
)
),
FIND("FLOOR",AC11),
IF(
NOT(
ISERROR(
FIND(" RM ",AC11)
)
),
FIND(" RM ",AC11),
IF(
NOT(
ISERROR(
FIND(" ROOM ",AC11)
)
),
FIND(" ROOM ",AC11),
IF(
NOT(
ISERROR(
FIND("SUITE",AC11)
)
),
FIND("SUITE",AC11),
IF(
NOT(
ISERROR(
FIND("BLDG",AC11) <- here's the deepest call level,
8 nested levels
)
),
FIND("BLDG",AC11)
)
)
)
)
)
)

You can't go 8 nested levels deep, so Excel correctly reports this as a
formula syntax error. If this were all you needed to check, the
simplistic fix would be replacing all NOT(ISERROR(...)) calls with
ISNUMBER(...) calls, using one fewer nested level.

However, you're seeking many different string constants in the same
cell. That makes it an obvious candidate for a lookup call. If you
could live with a zero return value if none of the substrings were
found, you could try

=FIND(INDEX({"FLR","FLOOR"," RM "," ROOM ","SUITE","BLDG",""},
MATCH(1,COUNTIF(AC11,"*"&{"FLR","FLOOR"," RM "," ROOM ","SUITE","BLDG",
""}&"*"),0))," "&AC11)-1

Note the inclusion of "" as the last entry in the array constants. If
you have to have "" as the return value when there are no matches, try

=IF(SUM(COUNTIF(AC11,"*"&{"FLR","FLOOR"," RM "," ROOM ","SUITE","BLDG"}
&"*")),FIND(INDEX({"FLR","FLOOR"," RM "," ROOM
","SUITE","BLDG"},MATCH(1,
COUNTIF(AC11,"*"&{"FLR","FLOOR"," RM "," ROOM
","SUITE","BLDG"}&"*"),0)),
AC11),"")
Also - I cannot seem to get this FIND to work to locate occurences of
#. Any suggestions on that are welcome.

IF(NOT(ISERROR(FIND("#",AC1115))),FIND("#",AC1115)," ")
Click to expand...

If I enter "foo#bar" in cell AC1115, FIND("#",AC1115) returns 4, and
the formula above also returns 4. What are the *EXACT* contents of your
cell AC1115?
 
Top