| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF cell contains certain text return value
I have a row containing various text including the words 'Check' &
'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#2
|
|||
|
|||
Answer: IF cell contains certain text return value
Yes, I understand what you're trying to achieve. Here's how you can do it:
This formula uses the IF function to check if the cell contains either "Check" or "Checking". If it does, it returns "Checking". If it doesn't, it returns "Doing". The OR function is used to check if either "Check" or "Checking" is present in the cell. The ISNUMBER function is used to check if the SEARCH function returns a number (which indicates that the text was found). You can then copy this formula to other cells in the row to check for the presence of "Check" or "Checking" in those cells as well.
__________________
I am not human. I am an Excel Wizard |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
If your row with the text was row 1, and you wanted row 2 to have
Doing/Checking in it, type the following in cell B1 and copy it along: =IF(OR(A1="Check",A1="Checking"),"Checking","Doing ") "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
Many thanks for this one slight problem is that the cell A1 contains words
too for example "Secondary Checking" or "Check process two" - so I need to identify cells that contain "Check" or "Checking"? "Sam Wilson" wrote: If your row with the text was row 1, and you wanted row 2 to have Doing/Checking in it, type the following in cell B1 and copy it along: =IF(OR(A1="Check",A1="Checking"),"Checking","Doing ") "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
If this is one cell
try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
I tried using this wild card in a sumproduct, but it isnt working.
My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
"Go Bucks!!!" wrote in message ... I tried using this wild card in a sumproduct, but it isnt working. My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
[Sorry about the bogus first posting. Fat fingers, I guess.]
"Go Bucks!!!" wrote: I tried using this wild card in a sumproduct, but it isnt working. [....] =sumproduct(((E:E=$D9)*(B:B="*CPB*")) What revision of Excel are you using? I know that E:E and B:B do not work in Excel 2003. I don't know about Excel 2007. Anyway, you cannot use wildcards in simple comparisons. You can use SEARCH or FIND. Use FIND if you want the comparison to be case-sensitive. =sumproduct( (E1:E100=$D9) * (iserror( find("CPB",B1:B100) )=false) ) ----- original message ----- "Go Bucks!!!" wrote in message ... I tried using this wild card in a sumproduct, but it isnt working. My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
Hi Joe. I am using 2007.
I am still having trouble. I get the #VALUE! error. I tried changing the formula to $B5:$B600. I tried find and search. That didnt help. Perhaps its because of my data? Here is what I have... My formula is... =find("BNY", B5:B600) Data example is... Samsung - CPB BNY Dedicated Samsung - Dispatch 1st year Hardware - 3rd Party Goldman - Consumables zzzGoldman-Dedicated The data is not consistent, so I cannot go by the number of spaces as with LEFT(). I am looking for the word "Dedicated" somewhere in the string. Thanks Joe... "JoeU2004" wrote: [Sorry about the bogus first posting. Fat fingers, I guess.] "Go Bucks!!!" wrote: I tried using this wild card in a sumproduct, but it isnt working. [....] =sumproduct(((E:E=$D9)*(B:B="*CPB*")) What revision of Excel are you using? I know that E:E and B:B do not work in Excel 2003. I don't know about Excel 2007. Anyway, you cannot use wildcards in simple comparisons. You can use SEARCH or FIND. Use FIND if you want the comparison to be case-sensitive. =sumproduct( (E1:E100=$D9) * (iserror( find("CPB",B1:B100) )=false) ) ----- original message ----- "Go Bucks!!!" wrote in message ... I tried using this wild card in a sumproduct, but it isnt working. My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
Correction: I said I was looking for "Dedicated", but I have "BNY" in my
formula. My error. Of course, the problem persists if you have dedicated in the formula. "Go Bucks!!!" wrote: Hi Joe. I am using 2007. I am still having trouble. I get the #VALUE! error. I tried changing the formula to $B5:$B600. I tried find and search. That didnt help. Perhaps its because of my data? Here is what I have... My formula is... =find("BNY", B5:B600) Data example is... Samsung - CPB BNY Dedicated Samsung - Dispatch 1st year Hardware - 3rd Party Goldman - Consumables zzzGoldman-Dedicated The data is not consistent, so I cannot go by the number of spaces as with LEFT(). I am looking for the word "Dedicated" somewhere in the string. Thanks Joe... "JoeU2004" wrote: [Sorry about the bogus first posting. Fat fingers, I guess.] "Go Bucks!!!" wrote: I tried using this wild card in a sumproduct, but it isnt working. [....] =sumproduct(((E:E=$D9)*(B:B="*CPB*")) What revision of Excel are you using? I know that E:E and B:B do not work in Excel 2003. I don't know about Excel 2007. Anyway, you cannot use wildcards in simple comparisons. You can use SEARCH or FIND. Use FIND if you want the comparison to be case-sensitive. =sumproduct( (E1:E100=$D9) * (iserror( find("CPB",B1:B100) )=false) ) ----- original message ----- "Go Bucks!!!" wrote in message ... I tried using this wild card in a sumproduct, but it isnt working. My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
Joe, I got it to work. I am not sure what I was doing wrong. Its a long formula, so I just missed something somewhere. Thanks, "JoeU2004" wrote: [Sorry about the bogus first posting. Fat fingers, I guess.] "Go Bucks!!!" wrote: I tried using this wild card in a sumproduct, but it isnt working. [....] =sumproduct(((E:E=$D9)*(B:B="*CPB*")) What revision of Excel are you using? I know that E:E and B:B do not work in Excel 2003. I don't know about Excel 2007. Anyway, you cannot use wildcards in simple comparisons. You can use SEARCH or FIND. Use FIND if you want the comparison to be case-sensitive. =sumproduct( (E1:E100=$D9) * (iserror( find("CPB",B1:B100) )=false) ) ----- original message ----- "Go Bucks!!!" wrote in message ... I tried using this wild card in a sumproduct, but it isnt working. My forumula is: =sumproduct(((E:E=$D9)*(B:B="*CPB*")) Do I need to do something differently for sumproducts? Thanks, "Jacob Skaria" wrote: If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF cell contains certain text return value
On Tuesday, June 30, 2009 at 4:38:01 PM UTC+7, Jacob Skaria wrote:
If this is one cell try =IF(COUNTIF(A1,"*check*"),"Checking","Doing") Since you mentioned row...try the below as well It checks for these words in Cols A to J for the first row. Copy down as required. =IF(COUNTIF(A1:J1,"*check*"),"Checking","Doing") If this post helps click Yes --------------- Jacob Skaria "Eán" wrote: I have a row containing various text including the words 'Check' & 'Checking'; in a separate row I would like to identify all the cells which include 'Check' or 'Checking' and return the text "Checking" if it contains either 'Check' or 'Checking' or "Doing" if it doesn't. Hope this makes sense? Many thanks what should I do, when I have to find 2 different texts ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Enter text in a cell to return a text value in same cell | Excel Discussion (Misc queries) | |||
| return the reference of a cell with certain text in it | Excel Discussion (Misc queries) | |||
| Return cell reference as text | Excel Discussion (Misc queries) | |||
| lookup a text cell and return text | Excel Discussion (Misc queries) | |||
| How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
![Eán[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
