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

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


"Jonathan Horvath" wrote:

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

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

"Elkar" wrote:

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


"Jonathan Horvath" wrote:

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

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

"Toppers" wrote in message
...
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))

"Elkar" wrote:

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


"Jonathan Horvath" wrote:

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

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

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

Biff

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Try this:

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


"Jonathan Horvath" wrote:

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

Another way...

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


"Jonathan Horvath" wrote:

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

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
--
*****
birds of the same feather flock together..



"Jonathan Horvath" wrote:

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

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

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

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

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

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

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" wrote:

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

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

On Wed, 20 Jun 2007 13:04:08 -0700, Jonathan Horvath
wrote:

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