Complex criteria for flagging text

[JonathanT]

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

Thanks for your help.
-Jonathan

 

[Marcelo Branco]

Welcome to Mr Excel

Try something like


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

K, 1, 2​

[/td][td]

TRUE​

[/td][/tr]


[tr][td]

2​

[/td][td]

9, 10, 11​

[/td][td]

FALSE​

[/td][/tr]


[tr][td]

3​

[/td][td]

K, 1, 2, 10, 11​

[/td][td]

TRUE​

[/td][/tr]
[/table]

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

Hope this helps

M.

 

[JonathanT]

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!

 

[Marcelo Branco]

The formula worked perfectly for me in all cases above


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

K, 1, 2​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

9, 10, 11​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

K, 1, 2, 10, 11​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

K, 1​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1, 2​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[JonathanT]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

K, 1, 2​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

9, 10, 11​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

K, 1, 2, 10, 11​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

K, 1​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1, 2​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[Marcelo Branco]

You are welcome.

M.