If Cell Contains Text return a value

[russborup]

Hello All

I receive an excel file where a cell would contain a series of acronyms separated by a comma.
Is there a formula that will return a result if a portion of a cell equals the query, or will I just have to stick with text to columns and then an if(or statement.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]AB,CNS,RDSH,BTBC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
iF A1 CONTAINS "CNS' RETURN A VALUE OF "Y"

I would appreciate any assistance

Russ

 

[JoeMo]

Excel Workbook
	A	B
1	AB,CNS,RDSH,BTBC	Y
Sheet7

 

[jim may]

Maybe like...

Excel 2010

	A	B	C
My Data	Query	Found?

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AB,CNS,RDSH,BTBC[/TD]
[TD="align: center"]CNS[/TD]
[TD="align: center"]Y[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(SEARCH(B2,A2),"Y","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

C1 is hard-coding the criteria in the formula, F2 uses a cell reference for the criteria:

Book1
	A	B	C	D	E	F
1	AB,CNS,RDSH,BTBC		Y		Find What	Result
2					CNS	Y
Sheet112

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(SEARCH("CNS",A1)),"Y","N")
F2	=IF(ISNUMBER(SEARCH(E2,A1)),"Y","N")

 

[russborup]

Thank you!!!

Sheet7

	A	B
AB,CNS,RDSH,BTBC	Y

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:127px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(ISNUMBER(FIND("CNS",A1)),"Y","N") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[russborup]

Thank you!

Hi,

C1 is hard-coding the criteria in the formula, F2 uses a cell reference for the criteria:

	A	B	C	D	E	F
AB,CNS,RDSH,BTBC	Y	Find What	Result
CNS	Y

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet112​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH("CNS",A1)),"Y","N")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(E2,A1)),"Y","N")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[russborup]

Thank you!!

Maybe like...

Excel 2010

	A	B	C
My Data	Query	Found?

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AB,CNS,RDSH,BTBC[/TD]
[TD="align: center"]CNS[/TD]
[TD="align: center"]Y[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF(SEARCH(B2,A2),"Y","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

You're welcome.

Just to point out a couple of things.

The FIND function is case-sensitive.
Without the ISNUMBER check, if the Criteria is Not found, the formula will Error out.

 

[Rick Rothstein]

We do not know your data, but one thing to watch out for with the solutions provided above is a query for an acronym that can also be found within a longer acronym. For example (and this is completely made up to show you the possible problem), if you search for the AB acronym but your cell contained something like this...

CAB,XYZ,MYP

If you searched just for AB, it will be found because it exists as part of the CAB acronym. If your data could have such embedded acronyms, then you will need to use this formula instead...

=IF(ISNUMBER(SEARCH(","&B2&",",","&A2&",")),"Y","")

 

[russborup]

Thank you!
This does help as my Data for this issue is evolving.

We do not know your data, but one thing to watch out for with the solutions provided above is a query for an acronym that can also be found within a longer acronym. For example (and this is completely made up to show you the possible problem), if you search for the AB acronym but your cell contained something like this...

CAB,XYZ,MYP

If you searched just for AB, it will be found because it exists as part of the CAB acronym. If your data could have such embedded acronyms, then you will need to use this formula instead...

=IF(ISNUMBER(SEARCH(","&B2&",",","&A2&",")),"Y","")