| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Text in cells
Hi
I need a formula that will confirm if certain text that is in one cell is contained in another cell ie Cell A1 Oranges Apples Bananas Cell B1 Apples Cell C1 I need a formula to return that B1 was found in A1. The formula I'm using is =IF(FIND(B1,A1)<"","yes","") This works fine when the word in B1 is contained in A1 but I'm getting #VALUE when the word is not in A1 Can anyone help? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Text in cells
Try this:
=IF(ISERROR(FIND(B1,A1)),"","yes") Note that FIND is case sensitive, so "Apples" would be found, but "apples" would not. Replacing FIND with SEARCH would ignore case. HTH, Elkar "jimar" wrote: Hi I need a formula that will confirm if certain text that is in one cell is contained in another cell ie Cell A1 Oranges Apples Bananas Cell B1 Apples Cell C1 I need a formula to return that B1 was found in A1. The formula I'm using is =IF(FIND(B1,A1)<"","yes","") This works fine when the word in B1 is contained in A1 but I'm getting #VALUE when the word is not in A1 Can anyone help? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Text in cells
With
A1: Oranges Apples Bananas and B1: apples This formula test if A1 contains the text from cell B1: C1: =IF(COUNTIF(A1,"*"&B1&"*"),"Yes","") Note: COUNTIF is NOT case-sensitive Does that help? *********** Regards, Ron XL2002, WinXP "jimar" wrote: Hi I need a formula that will confirm if certain text that is in one cell is contained in another cell ie Cell A1 Oranges Apples Bananas Cell B1 Apples Cell C1 I need a formula to return that B1 was found in A1. The formula I'm using is =IF(FIND(B1,A1)<"","yes","") This works fine when the word in B1 is contained in A1 but I'm getting #VALUE when the word is not in A1 Can anyone help? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Text in cells
Thanks this works great
"Elkar" wrote: Try this: =IF(ISERROR(FIND(B1,A1)),"","yes") Note that FIND is case sensitive, so "Apples" would be found, but "apples" would not. Replacing FIND with SEARCH would ignore case. HTH, Elkar "jimar" wrote: Hi I need a formula that will confirm if certain text that is in one cell is contained in another cell ie Cell A1 Oranges Apples Bananas Cell B1 Apples Cell C1 I need a formula to return that B1 was found in A1. The formula I'm using is =IF(FIND(B1,A1)<"","yes","") This works fine when the word in B1 is contained in A1 but I'm getting #VALUE when the word is not in A1 Can anyone help? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Text in cells
Yes it works fine. Thank you
"Ron Coderre" wrote: With A1: Oranges Apples Bananas and B1: apples This formula test if A1 contains the text from cell B1: C1: =IF(COUNTIF(A1,"*"&B1&"*"),"Yes","") Note: COUNTIF is NOT case-sensitive Does that help? *********** Regards, Ron XL2002, WinXP "jimar" wrote: Hi I need a formula that will confirm if certain text that is in one cell is contained in another cell ie Cell A1 Oranges Apples Bananas Cell B1 Apples Cell C1 I need a formula to return that B1 was found in A1. The formula I'm using is =IF(FIND(B1,A1)<"","yes","") This works fine when the word in B1 is contained in A1 but I'm getting #VALUE when the word is not in A1 Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can i find cells which contain certain text? | Excel Worksheet Functions | |||
| How to find text in a range of cells? | Excel Discussion (Misc queries) | |||
| How to find cells containing exact text, and separate them | Excel Worksheet Functions | |||
| Find/Replace Text In Cells | Excel Discussion (Misc queries) | |||
| INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions | |||
Linear Mode
