| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2)
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2)
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2)
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2)
Thanks. Very helpful!
My life saver. :D 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2) - my further examples
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If the cell contains..., do something (2)
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
| Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| cell color index comparison | New Users to Excel | |||
Hybrid Mode
