software

If the cell contains..., do something

Hi.

1)
How to do the following:
a) If the cell starts with quot;seniorquot;, do something... (eg count that cell if the
cell starts with quot;seniorquot;)
b) If the cell has quot;Marchquot; in the middle, do something...
c) If the cell ends with quot;boyquot;, 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 quot;conditional formattingquot;?
I need these conditons to format my cells too!
Thank you!

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

I'm not sure how you could do this with conditional formatting, which is
what you need for the last part of your question.
However, you also need to say do a count, so try the following for
point 1)
a) =IF(LEFT(A1,6)=quot;seniorquot;,quot;Yesquot;,quot;quot;)
b) =IF(ISERR(IF(SEARCH(quot;Marchquot;,A1,1)gt;0,1,quot;quot;)),quot;quot;,quot;Yes quot;)
c) =IF(RIGHT(A1,3)=quot;boyquot;,quot;Yesquot;,quot;quot;)
d) As b) which just finds the word March anywhere in the text.
Hope this helps.
Clive

0-0 Wai Wai ^-^ Wrote:
gt; If the cell contains..., do something
gt;
gt; Hi.
gt;
gt; 1)
gt; How to do the following:
gt; a) If the cell starts with quot;seniorquot;, do something... (eg count that
gt; cell if the
gt; cell starts with quot;seniorquot;)
gt; b) If the cell has quot;Marchquot; in the middle, do something...
gt; c) If the cell ends with quot;boyquot;, do something...
gt; d) If the cell contains the word (it doesn't matter where the word
gt; locates), do
gt; something...
gt; ??
gt;
gt; 2) Can you answers in Q1 be used in quot;conditional formattingquot;?
gt; I need these conditons to format my cells too!
gt;--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: www.excelforum.com/member.php...oamp;userid=32569
View this thread: www.excelforum.com/showthread...hreadid=525176
quot;Clivey_UKquot; gt; ¦b¶l¥ó¤¤¼¶¼g...
gt;
gt; I'm not sure how you could do this with conditional formatting, which is
gt; what you need for the last part of your question.
gt; However, you also need to say do a count, so try the following for
gt; point 1)
gt; a) =IF(LEFT(A1,6)=quot;seniorquot;,quot;Yesquot;,quot;quot;)
gt; b) =IF(ISERR(IF(SEARCH(quot;Marchquot;,A1,1)gt;0,1,quot;quot;)),quot;quot;,quot;Yes quot;)
gt; c) =IF(RIGHT(A1,3)=quot;boyquot;,quot;Yesquot;,quot;quot;)
gt; d) As b) which just finds the word March anywhere in the text.
gt; Hope this helps.
gt; Clive

As to (b), my intention is to find word which happens in the middle only! Eg:
- March 30
- 30 March
- 2 March., 2000

The first one: Wrong! The word is at the start!
The second one: Wrong! The word is at the end!
Only the last one is true.

Any way to do so?
Thank you!

Try this to find March when not at start, not at end, but appears in
text:
=IF(ISERR(IF(SEARCH(quot;Marchquot;,A1,2)gt;1,1,quot;quot;)),quot;quot;,IF(R IGHT(A1,5)lt;gt;quot;Marchquot;,quot;Yesquot;,quot;quot;))
Clive

0-0 Wai Wai ^-^ Wrote:
gt; quot;Clivey_UKquot; gt;
gt; ¦b¶l¥ó
gt; ¤¤¼¶¼g...
gt; gt;
gt; gt; I'm not sure how you could do this with conditional formatting, which
gt; is
gt; gt; what you need for the last part of your question.
gt; gt; However, you also need to say do a count, so try the following for
gt; gt; point 1)
gt; gt; a) =IF(LEFT(A1,6)=quot;seniorquot;,quot;Yesquot;,quot;quot;)
gt; gt; b) =IF(ISERR(IF(SEARCH(quot;Marchquot;,A1,1)gt;0,1,quot;quot;)),quot;quot;,quot;Yes quot;)
gt; gt; c) =IF(RIGHT(A1,3)=quot;boyquot;,quot;Yesquot;,quot;quot;)
gt; gt; d) As b) which just finds the word March anywhere in the text.
gt; gt; Hope this helps.
gt; gt; Clive
gt;
gt; As to (b), my intention is to find word which happens in the middle
gt; only! Eg:
gt; - March 30
gt; - 30 March
gt; - 2 March., 2000
gt;
gt; The first one: Wrong! The word is at the start!
gt; The second one: Wrong! The word is at the end!
gt; Only the last one is true.
gt;
gt; Any way to do so?
gt; Thank you!--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: www.excelforum.com/member.php...oamp;userid=32569
View this thread: www.excelforum.com/showthread...hreadid=525176On Wed, 22 Mar 2006 17:21:52 0800, quot;0-0 Wai Wai ^-^quot; gt; wrote:

gt;If the cell contains..., do something
gt;
gt;Hi.
gt;
gt;1)
gt;How to do the following:
gt;a) If the cell starts with quot;seniorquot;, do something... (eg count that cell if the
gt;cell starts with quot;seniorquot;)

=LEFT(A1,7) = quot;senior quot;

=IF(LEFT(A1,7) = quot;senior quot;,quot;Do Somethingquot;,quot;Do Nothingquot;)

gt;b) If the cell has quot;Marchquot; in the middle, do something...

=ISNUMBER(FIND(quot; March quot;,A1)) for conditional formatting.

(note the space before and after March in the FIND function).

=IF(ISNUMBER(FIND(quot; March quot;,A1)),quot;do somethingquot;,quot;DO NOTHINGquot;)

gt;c) If the cell ends with quot;boyquot;, do something...
gt;d) If the cell contains the word (it doesn't matter where the word locates), do
gt;something...
gt;??
gt;
gt;2) Can you answers in Q1 be used in quot;conditional formattingquot;?
gt;I need these conditons to format my cells too!
gt;Thank you!

--ron


Thanks!
As to b, my intention is it returns true only if the string is in the middle
only.
- 12 Mar
- Mar 12
-12 Mar 1999
The first two is false since quot;Marquot; is at the end amp; start respectively.
Only the third one is counted.

Any help?quot;Clivey_UKquot; gt; ¦b¶l¥ó¤¤¼¶¼g...
gt;
gt; I'm not sure how you could do this with conditional formatting, which is
gt; what you need for the last part of your question.
gt; However, you also need to say do a count, so try the following for
gt; point 1)
gt; a) =IF(LEFT(A1,6)=quot;seniorquot;,quot;Yesquot;,quot;quot;)
gt; b) =IF(ISERR(IF(SEARCH(quot;Marchquot;,A1,1)gt;0,1,quot;quot;)),quot;quot;,quot;Yes quot;)
gt; c) =IF(RIGHT(A1,3)=quot;boyquot;,quot;Yesquot;,quot;quot;)
gt; d) As b) which just finds the word March anywhere in the text.
gt; Hope this helps.
gt; Clive
gt;
gt; 0-0 Wai Wai ^-^ Wrote:
gt; gt; If the cell contains..., do something
gt; gt;
gt; gt; Hi.
gt; gt;
gt; gt; 1)
gt; gt; How to do the following:
gt; gt; a) If the cell starts with quot;seniorquot;, do something... (eg count that
gt; gt; cell if the
gt; gt; cell starts with quot;seniorquot;)
gt; gt; b) If the cell has quot;Marchquot; in the middle, do something...
gt; gt; c) If the cell ends with quot;boyquot;, do something...
gt; gt; d) If the cell contains the word (it doesn't matter where the word
gt; gt; locates), do
gt; gt; something...
gt; gt; ??
gt; gt;
gt; gt; 2) Can you answers in Q1 be used in quot;conditional formattingquot;?
gt; gt; I need these conditons to format my cells too!
gt; gt;
gt;
gt;
gt; --
gt; Clivey_UK
gt; ------------------------------------------------------------------------
gt; Clivey_UK's Profile:
www.excelforum.com/member.php...oamp;userid=32569
gt; View this thread: www.excelforum.com/showthread...hreadid=525176
gt;