Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
If the cell contains..., do something
Hi. 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, 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
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)="senior","Yes","") b) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ") c) =IF(RIGHT(A1,3)="boy","Yes","") d) As b) which just finds the word March anywhere in the text. Hope this helps. Clive 0-0 Wai Wai ^-^ Wrote: If the cell contains..., do something Hi. 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, 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! -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=525176 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
"Clivey_UK" ¦b¶l¥ó ¤¤¼¶¼g... 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)="senior","Yes","") b) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ") c) =IF(RIGHT(A1,3)="boy","Yes","") d) As b) which just finds the word March anywhere in the text. Hope this helps. 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
Try this to find March when not at start, not at end, but appears in text: =IF(ISERR(IF(SEARCH("March",A1,2)1,1,"")),"",IF(R IGHT(A1,5)<"March","Yes","")) Clive 0-0 Wai Wai ^-^ Wrote: "Clivey_UK" ¦b¶l¥ó ¤¤¼¶¼g... 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)="senior","Yes","") b) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ") c) =IF(RIGHT(A1,3)="boy","Yes","") d) As b) which just finds the word March anywhere in the text. Hope this helps. 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! -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=525176 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
On Wed, 22 Mar 2006 17:21:52 +0800, "0-0 Wai Wai ^-^" wrote:
If the cell contains..., do something Hi. 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") =LEFT(A1,7) = "senior " =IF(LEFT(A1,7) = "senior ","Do Something","Do Nothing") b) If the cell has "March" in the middle, do something... =ISNUMBER(FIND(" March ",A1)) for conditional formatting. (note the space before and after March in the FIND function). =IF(ISNUMBER(FIND(" March ",A1)),"do something","DO NOTHING") 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! --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If the cell contains..., do something
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 "Mar" is at the end & start respectively. Only the third one is counted. Any help? "Clivey_UK" ¦b¶l¥ó ¤¤¼¶¼g... 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)="senior","Yes","") b) =IF(ISERR(IF(SEARCH("March",A1,1)0,1,"")),"","Yes ") c) =IF(RIGHT(A1,3)="boy","Yes","") d) As b) which just finds the word March anywhere in the text. Hope this helps. Clive 0-0 Wai Wai ^-^ Wrote: If the cell contains..., do something Hi. 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, 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! -- Clivey_UK ------------------------------------------------------------------------ Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569 View this thread: http://www.excelforum.com/showthread...hreadid=525176 |
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 |