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

[0-0 Wai Wai ^-^]

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!

 

[Ron Coderre]

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 ^-^]

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

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

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!

 

[Ron Coderre]

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

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

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

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!

 

[0-0 Wai Wai ^-^]

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.

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

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

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


:


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!

 

[0-0 Wai Wai ^-^]

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.

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

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

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


:


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!

 

[0-0 Wai Wai ^-^]

Thanks. It helps!

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.

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

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

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


:


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!