If the cell contains..., do something (2)

Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell if the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word locates), do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell if the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word locates), do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

I couldn't believe my eyes - the power of quotes.
It's just that easy. I tried similar before in vain.
It might be due to the fact I forget the quotes!

But it opens some other problems.
If the content/value I lookup contains ? and *, how can I do?
Eg:
???
dunno?
*_* Good!
*IMPORTANT*
**?**

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell if
the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word locates),
do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

To match wildcards, prefix them with a tilde (~).

Examples:
B1: =COUNTIF(A1,"*~?")=1
Tests if cell A1 ends with a question mark.

B1: =COUNTIF(A1,"~**~**")=1
Tests if cell A1 starts with an asterisk and contains another asterisk

Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:

I couldn't believe my eyes - the power of quotes.
It's just that easy. I tried similar before in vain.
It might be due to the fact I forget the quotes!

But it opens some other problems.
If the content/value I lookup contains ? and *, how can I do?
Eg:
???
dunno?
*_* Good!
*IMPORTANT*
**?**

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell if
the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word locates),
do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

Thanks. Very helpful!
My life saver.

Another question (it should be the last, hopefully :
- does it matter if I type in capital or not (eg ABC vs abc)?

If not, how can a make a search which is case-sensitive?



--
Additional information:
- I'm using Office XP
- I'm using Windows XP

¥»¤Hªº¯à¤O«D±`¦³**. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.
"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
To match wildcards, prefix them with a tilde (~).

Examples:
B1: =COUNTIF(A1,"*~?")=1
Tests if cell A1 ends with a question mark.

B1: =COUNTIF(A1,"~**~**")=1
Tests if cell A1 starts with an asterisk and contains another asterisk

Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:

I couldn't believe my eyes - the power of quotes.
It's just that easy. I tried similar before in vain.
It might be due to the fact I forget the quotes!

But it opens some other problems.
If the content/value I lookup contains ? and *, how can I do?
Eg:
???
dunno?
*_* Good!
*IMPORTANT*
**?**

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


"Ron Coderre" |b?l¢Do
?????g...
These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell
if
the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the
start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word
locates),
do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

To make this thread more complete, I add some more examples in addition to Ron
Coderre's:
------------------------------------------------------------

Note:
To match wildcards, prefix them with a tilde (~).

Examples:
B1: =COUNTIF(A1,"~?~?~?")=1
Tests if cell A1 contains ???.

B1: =COUNTIF(A1,"""I am great!""")=1
Tests if cell A1 contains "I am great!" (quotes included).

B1: =COUNTIF(A1,"*~?")=1
Tests if cell A1 ends with a question mark.

B1: =COUNTIF(A1,"~**~**")=1
Tests if cell A1 starts with an asterisk and contains another asterisk.


"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
To match wildcards, prefix them with a tilde (~).

Examples:
B1: =COUNTIF(A1,"*~?")=1
Tests if cell A1 ends with a question mark.

B1: =COUNTIF(A1,"~**~**")=1
Tests if cell A1 starts with an asterisk and contains another asterisk

Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:

I couldn't believe my eyes - the power of quotes.
It's just that easy. I tried similar before in vain.
It might be due to the fact I forget the quotes!

But it opens some other problems.
If the content/value I lookup contains ? and *, how can I do?
Eg:
???
dunno?
*_* Good!
*IMPORTANT*
**?**

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


"Ron Coderre" |b?l¢Do
?????g...
These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell
if
the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the
start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word
locates),
do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!

Thanks. It helps! :P

Another question.
Does it matter what case I type in the COUNT test?
If not, is it possible to make a COUNT test which is case-sensitive?

Thank you.



"Ron Coderre" ¦b¶l¥ó
¤¤¼¶¼g...
To match wildcards, prefix them with a tilde (~).

Examples:
B1: =COUNTIF(A1,"*~?")=1
Tests if cell A1 ends with a question mark.

B1: =COUNTIF(A1,"~**~**")=1
Tests if cell A1 starts with an asterisk and contains another asterisk

Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:

I couldn't believe my eyes - the power of quotes.
It's just that easy. I tried similar before in vain.
It might be due to the fact I forget the quotes!

But it opens some other problems.
If the content/value I lookup contains ? and *, how can I do?
Eg:
???
dunno?
*_* Good!
*IMPORTANT*
**?**

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


"Ron Coderre" |b?l¢Do
?????g...
These are my suggestions...

Each of these formulas can be used in the "Formula is" section of
Conditional Formatting. But, conditional formatting only allows 3 distinct
conditionas, apart from the default:

For a value in A1

1a) Starts with "senior"
B1: =COUNTIF(A1,"senior*")=1

1b) Contains "march" in the middle, but not at either end
B1: =COUNTIF(A1,"*?march?*")=1

1c) Ends with "boy"
B1: =COUNTIF(A1,"*boy")=1

1d) Contains "boy" anywhere
B1: =COUNTIF(A1,"*boy*")=1


Does that help?

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

XL2002, WinXP-Pro


"0-0 Wai Wai ^-^" wrote:


Hi.
Here's my original question:

1)
How to do the following:
a) If the cell starts with "senior", do something... (eg count that cell
if
the
cell starts with "senior")
b) If the cell has "March" in the middle only (but NOT at the
start/end), do
something...
c) If the cell ends with "boy", do something...
d) If the cell contains the word (it doesn't matter where the word
locates),
do
something...
??

2) Can you answers in Q1 be used in "conditional formatting"?
I need these conditons to format my cells too!
Thank you!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP


==========================

Here's the answers I knew so far:

1)
a) =IF(LEFT(A1,6)="senior","Yes","")
b) [?????????]
c) =IF(RIGHT(A1,3)="boy","Yes","")
d) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ")

2) Sorry, no!
[?????????]

Thank you for your help!