IF cell contains certain text return value

[Eán]

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

 

[Sam Wilson]

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]

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"?

 

[Jacob Skaria]

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

 

[Go Bucks!!!]

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,

 

[JoeU2004]

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,

 

[JoeU2004]

[Sorry about the bogus first posting. Fat fingers, I guess.]

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!!!]

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...

[Sorry about the bogus first posting. Fat fingers, I guess.]

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 -----

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,

 

[Go Bucks!!!]

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.

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...

[Sorry about the bogus first posting. Fat fingers, I guess.]

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 -----

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,



:

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


:

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

 

[Go Bucks!!!]

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,

[Sorry about the bogus first posting. Fat fingers, I guess.]

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 -----

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,