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

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

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

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

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

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

On Sat, 23 Jun 2007 05:20:00 -0700, Jonathan Horvath
wrote:

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

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

On Sun, 24 Jun 2007 04:49:00 -0700, Jonathan Horvath
wrote:

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

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

"Ron Rosenfeld" wrote:

On Sun, 24 Jun 2007 04:49:00 -0700, Jonathan Horvath
wrote:

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

On Sun, 24 Jun 2007 16:13:01 -0700, Jonathan Horvath
wrote:

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

Let me know!!

There can still be problems if you have data that includes a high priority ID
that is embedded in such a way that it should not be counted. But you'll need
to examine your data set to see if that is a problem. (There are solutions
even for that).


--ron

Ron,

It worked. I have several entries that have multiple identifiers however
using the prioritization array allowed me to pluck the unique ones off one by
one and the remaining ones were all grouped into a single, and appropriate,
bucket.

thanks!

Jonathan

On Mon, 25 Jun 2007 11:04:01 -0700, Jonathan Horvath
wrote:

Ron,

It worked. I have several entries that have multiple identifiers however
using the prioritization array allowed me to pluck the unique ones off one by
one and the remaining ones were all grouped into a single, and appropriate,
bucket.

thanks!

Jonathan


Good to hear. Thanks for the feedback.
--ron