Limit to nested IF(ISNUMBER(SEARCH)) functions?

[Guest]

is there a limit to the number of nests you can have in a nested
IF(ISNUMBER(SEARCH)) functions?

For example, I have one that works properly with 6; for example:

=IF(ISNUMBER(SEARCH("jon",AC2)),"jon",IF(ISNUMBER(SEARCH("bill",AC2)),"bill",IF(ISNUMBER(SEARCH("tom",AC2)),"tom",IF(ISNUMBER(SEARCH("joe",AC2)),"joe",IF(ISNUMBER(SEARCH("rob",AC2)),"rob",IF(ISNUMBER(SEARCH("ted",AC2)),"ted",""))))))


if I add another nest, Excel finds an error with the last search and will
not allow it...

Thanks in advance for your help,

Jonathan

 

[Guest]

Yes, Excel is limited to 7 "layers" of nesting. Here's an alternative
approach to your formula:

=IF(ISNUMBER(SEARCH("jon",AC2)),"jon","") &
IF(ISNUMBER(SEARCH("bill",AC2)),"bill","") &
IF(ISNUMBER(SEARCH("tom",AC2)),"tom","") & etc....

HTH,
Elkar

 

[Guest]

Alternative ...

Set names (jon,bill etc) in column A and numbers 1,2 etc in column B

A B
jon 1
bill 2
tom 3
etc

Use the following formula:

=INDEX($A$1:$A$10,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$10,AC2)))*$B$1:$B$10))

 

[Guest]

Try this:

=IF(OR(A2={"bill","joe","jon","rob","ted","tom"}),A2,"")

 

[Guest]

Another way...

=IF(SUM(COUNTIF(A2,{"bill","joe","jon","rob","ted","tom"})),A2,"")

 

[T. Valko]

Another way:

=LOOKUP(2,1/(ISNUMBER(SEARCH(A1:A10,AC2))),A1:A10)

If more than 1 name is in the cell the formula will match the last one:

AC2 = Jon talked to Tom

Formula result = Tom

Biff

 

[Rick Rothstein \(MVP - VB\)]

Biff

Off topic to this thread.... but I was wondering if you had a chance to
revisit the "Subject: In excel, I want to generate a random number" thread
over in the microsoft.public.excel.misc newsgroup? You made a suggestion to
which I posted some reply code that I think you may find interesting. If you
revisit it, please continue postings over there. Thanks.

Rick

 

[T. Valko]

I haven't had a chance to play around with that. I'll do that some time
tonight.

Biff

 

[Rick Rothstein \(MVP - VB\)]

I haven't had a chance to play around with that. I'll do that

some time tonight.

Thanks... I thought you might have just lost track of the thread.

Rick

 

[Guest]

Hello Jonathan,
your search function may be good, how about if the cell contain "bill rob
joe", what result do you need to be displayed ? If you are sure that there
will be no duplicates of one name among your list...you can be assured of the
formulas provided from the helpful members.
regards,
driller

 

[Guest]

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

 

[Ron Rosenfeld]

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

Are all the strings you'll be searching consist of names as above?

Will all the 10 different unique strings for which you'll be searching to be
found in the beginning letters of the searched string as above?


--ron

 

[Ron Rosenfeld]

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

If the unique strings can be found anywhere in the searched strings, you could
use this **array** formula.

First, set up a range where you list your 10 different unique strings. I
NAME'd it "Uniques".

Then, assuming your strings to search start in A2, enter the formula below as
an array formula. That means to hold down <ctrl><shift> while you hit <enter>.
Excel will place braces {...} around the formula

=IF(OR(ISNUMBER(FIND(Uniques,A2))),
INDEX(Uniques,MATCH(TRUE,(ISNUMBER(
FIND(Uniques,A2))),0)),"")

Then fill down the 2000 entries.

If the unique strings are required to be at the beginning of the search string,
I'd probably use a UDF.
--ron

 

[Guest]

Hi Ron,

we're getting really close. the unique string can be anywhere in the
searched strings. however, the desired return may not contain something in
the unique string.

Col A desired return
william jones bill
bill smith bill
will robins bill
jon smith jon
d. robert burns rob
rob miller rob

so i need a translator in there as well, i could set up the "uniques" as you
suggest, but i'd need a second so that i would have

Col A uniques_ID uniques_return
william jones william bill
bill smith bill bill
will robins will bill
jon smith jon jon
d. robert burns robert rob
rob miller rob rob


so it would search Col A for the Uniques_ID string and if it finds it it
would then return the Uniques_return...

is that a possible variation on your suggestion?

Thanks!

Jonathan

 

[Ron Rosenfeld]

Hi Ron,

we're getting really close. the unique string can be anywhere in the
searched strings. however, the desired return may not contain something in
the unique string.

Col A desired return
william jones bill
bill smith bill
will robins bill
jon smith jon
d. robert burns rob
rob miller rob

so i need a translator in there as well, i could set up the "uniques" as you
suggest, but i'd need a second so that i would have

Col A uniques_ID uniques_return
william jones william bill
bill smith bill bill
will robins will bill
jon smith jon jon
d. robert burns robert rob
rob miller rob rob


so it would search Col A for the Uniques_ID string and if it finds it it
would then return the Uniques_return...

is that a possible variation on your suggestion?

Thanks!

That is pretty simple to do. You have your two columns of Uniques_ID and
Uniques_Return. You just need to return the value in the second column.

=IF(OR(ISNUMBER(FIND(Uniques_ID,A2))),
INDEX(uniques_return,MATCH(TRUE,(ISNUMBER(
FIND(Uniques_ID,A2))),0)),"")

(Array-entered as before).

You realize that since you are accepting finding the unique string anywhere in
the searched string, the following will occur, with "jon" as one of the
uniques:

michael jones --> jon





--ron

 

[Guest]

Yeah, that's the difficulty...

here are some different examples:

Name unique-ID unique-return
umts_r5.667.1 umts umts
gsm_r5.234.1.a gsm gsm
gsm_ra8.234.1 ra ajar
gsm_6.234.ezx ezx ezx

when i use the equation you provided, it returns gsm for anything that
begins with gsm... so the last 3 return gsm and not the appropriate return.

thanks!

Jonathan

 

[Ron Rosenfeld]

Yeah, that's the difficulty...

here are some different examples:

Name unique-ID unique-return
umts_r5.667.1 umts umts
gsm_r5.234.1.a gsm gsm
gsm_ra8.234.1 ra ajar
gsm_6.234.ezx ezx ezx

when i use the equation you provided, it returns gsm for anything that
begins with gsm... so the last 3 return gsm and not the appropriate return.

thanks!

Jonathan

Your examples are even more complicated to deal with, in that it appears that
there can be multiple unique_ID's in the string to be searched; and the
required unique_ID may be located anywhere within the string!

You will need some rules to determine which unique_ID you want returned when
multiple possible unique_ID's are present within the string.

The rules might include, for example, prioritizing the unique_ID's. They may
depend, in part, on position; on whether the unique_ID is embedded within other
numbers/letters or has some clear sort of separations (e.g. ".", "_", or
start/end of string); and so forth.

Once you develop these rules, they should be able to be implemented in Excel
(or more likely, in VBA).
--ron

 

[Guest]

Hi Ron,

Thanks, you're correct in that this is rather complicated. My thought was
to search for "ra" first and have that return "ajar" then "ezx" to return
"ezx", then "umts" etc for "umts" and so on... which is why i started with
the IF(isnumber(search)), it was working, but unfortunately I can't have
enough nests.

hmmmmm....

Jonathan

 

[Ron Rosenfeld]

Hi Ron,

Thanks, you're correct in that this is rather complicated. My thought was
to search for "ra" first and have that return "ajar" then "ezx" to return
"ezx", then "umts" etc for "umts" and so on... which is why i started with
the IF(isnumber(search)), it was working, but unfortunately I can't have
enough nests.

hmmmmm....

Jonathan

If your rules are that there is a priority order of unique-ID's, then all you
need to do is list them in that order in your lookup tables.

e.g.

Tables:

unique-ID unique-return
ra ajar
ezx ezx
umts umts
gsm gsm


Formula:

=IF(OR(ISNUMBER(FIND(unique_ID,A2))),
INDEX(unique_return,MATCH(TRUE,(ISNUMBER(
FIND(unique_ID,A2))),0)),"")

**ARRAY-ENTERED**
--ron

 

[Guest]

I have to wait until I get in the office tomorrow to double check with the
full list but I think this will work!

Thanks!!!

Jonathan