If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
If the cell contains..., do something (2)
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! |
#6
|
|||
|
|||
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
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
cell color index comparison | MINAL ZUNKE | New Users | 1 | June 30th, 2005 07:11 AM |
Possible Lookup Table | Karen | Worksheet Functions | 5 | June 8th, 2005 09:43 PM |
Copy cell format to cell on another worksht and update automatical | kevinm | Worksheet Functions | 21 | May 19th, 2005 11:07 AM |
Changing Cell References | Katrina | Worksheet Functions | 1 | September 22nd, 2004 05:37 PM |
Do you have what it takes... | Frank Kabel | Worksheet Functions | 1 | February 22nd, 2004 08:30 PM |