Search text within string

[samikusi]

Column A

YSE PANEL CTRL SSS N1 HP
SSS 14L-50B W/600 AMP CB

I would like to search for the text "sss" in a column of strings

Part Type is an array of the Column A

ISNUMBER(SEARCH("sss",Part_Type)). This returns a number if it finds the text "sss" in any of the strings. I would like it to return an error (not found) if it sees the text "cntrl" in the any of strings even if the string also contains "sss"

[a31185]

=IF(ISNUMBER(SEARCH("CTRL",Part_Type)),"Not Found",ISNUMBER(SEARCH("sss",Part_Type)))

I Typed "CTRL" instead of "cntrl" on a hunch. Search is case in-sensitive.

How is Part_Type defined?

[jraj1106]

Hi
Try this:

samikusi.xlsm

[abousetta]

Search is case insensitive

Find is case sensitive

Each has its uses.

[samikusi]

@ a31185
the formula you typed will still return value in A1 which contains both ctrl and sss. If it contains both I would like for the ISNUMBER function to return an error (#NA)

[samikusi]

@jraj1106. Thank you for the reply
The functionality I am requesting will be part of another formula which I am not using VB. so this code in VB will not work