I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

=IF(LOWER(LEFT(A1,4))="dean","ADK",IF(LOWER(LEFT(A 1,4))="dave","DJM","MWP"))

--
Kind regards,

Niek Otten

"CMIConnie" wrote in message
...
I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or
returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and
returns
"MWP" if there is anything else but Dean or Dave or David in the cell.
I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM".
Am I
on the right track with this? It seems like there are too many logigals
for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

"Niek Otten" wrote:

=IF(LOWER(LEFT(A1,4))="dean","ADK",IF(LOWER(LEFT(A 1,4))="dave","DJM","MWP"))

--
Kind regards,

Niek Otten

"CMIConnie" wrote in message
...
I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or
returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and
returns
"MWP" if there is anything else but Dean or Dave or David in the cell.
I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM".
Am I
on the right track with this? It seems like there are too many logigals
for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Bill

Hi Bill,

You can see I'm lazier than you a I tried to avoid holding the SHIFT key
:-)

--
Kind regards,

Niek Otten

"Bill Martin" wrote in message
...
On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it
out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or
returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and
returns
"MWP" if there is anything else but Dean or Dave or David in the cell.
I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM".
Am I
on the right track with this? It seems like there are too many logigals
for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Bill

On Thu, 2 Mar 2006 11:14:47 -0500, Bill Martin wrote:

On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Bill

-------------------

Oops -- for a formula, replace the "ucase" with "upper". I'm confusing
formula words with VBA words. Sorry... (and why are they different
anyhow?)

Bill

Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.

In article ,
Bill Martin wrote:

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Absolutely right, JE!

Thanks,

Niek

"JE McGimpsey" wrote in message
...
Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.

In article ,
Bill Martin wrote:

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

"Bill Martin" wrote:

On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A 1,4))="DAVE","DJM","MWP"))

Bill

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})* {1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"CMIConnie" wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

Thank you... I will try it :)

"Ron Coderre" wrote:

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})* {1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"CMIConnie" wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

So should "David" return DJM, too (based on "anything else but Dean or
Dave or David")?

Should "Deanna" return MWP?

If so, this should work for you:

=IF(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dean ","ADK",
IF(OR(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dave ",A1="David"),
"DJM","JWM"))


In article ,
CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

Thank you for your time... I will try this, too...

"JE McGimpsey" wrote:

So should "David" return DJM, too (based on "anything else but Dean or
Dave or David")?

Should "Deanna" return MWP?

If so, this should work for you:

=IF(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dean ","ADK",
IF(OR(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dave ",A1="David"),
"DJM","JWM"))


In article ,
CMIConnie wrote:

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)