# Complex criteria for flagging text

#### JonathanT

##### New Member
I need to create a formula that flags whether certain text is included in a cell, but only if that text occurs by itself, not embedded as part of a larger text string -- but it's ok if it occurs by itself and also embedded in a larger string.

For example, the cell contains a list of grades such as "K, 1, 2". I need to return a TRUE if it contains 1, either by itself or along with other grades, but not if 1 only occurs within 10, 11 or 12.

Below is an example of the results I need to generate:

K, 1, 2 - TRUE
9, 10, 11 - FALSE
K, 1, 2, 10, 11 - TRUE

-Jonathan

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Welcome to Mr Excel

Try something like

 A​ B​ 1​ K, 1, 2​ TRUE​ 2​ 9, 10, 11​ FALSE​ 3​ K, 1, 2, 10, 11​ TRUE​

Formula in B1 copied down
=ISNUMBER(SEARCH(",1,",","&SUBSTITUTE(A1," ","")&","))

Hope this helps

M.

Thank you Marcelo -- this is very close. There's one glitch though, which I'm not sure how to resolve as I'm not familiar with the substitute function.

The formula works as long as the "1" is surrounded by commas -- but it might also occur at the beginning or the end of the string, or by itself.

So for example, in situations such as

"K, 1"
"1, 2"
"1" [still a text string]

it is returning FALSE on the above examples, where I need TRUE.

Forgot to mention - I am working with Excel 2010

Thank you!

The formula worked perfectly for me in all cases above

 A​ B​ 1​ K, 1, 2​ TRUE​ 2​ 9, 10, 11​ FALSE​ 3​ K, 1, 2, 10, 11​ TRUE​ 4​ K, 1​ TRUE​ 5​ 1, 2​ TRUE​ 6​ 1​ TRUE​

<tbody>
</tbody>

M.

Sorry, you are correct - I had a typo. This is working for all cases.

Thank you so much.

The formula worked perfectly for me in all cases above

 A​ B​ 1​ K, 1, 2​ TRUE​ 2​ 9, 10, 11​ FALSE​ 3​ K, 1, 2, 10, 11​ TRUE​ 4​ K, 1​ TRUE​ 5​ 1, 2​ TRUE​ 6​ 1​ TRUE​

<tbody>
</tbody>

M.

You are welcome.

M.

Replies
23
Views
746
Replies
1
Views
488
Replies
18
Views
622
Replies
1
Views
166
Replies
2
Views
384

1,196,042
Messages
6,013,049
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back