Excel: how to list all cells from the range that contain two characters-CodePudding

I'd like to list all the cells that contain two characters within the string.

I tried something like this:

=AND( ISNUMBER(SEARCH("A";$C$2:$C$14)); ISNUMBER(SEARCH("T";$C$2:$C$14)))

but it does not work...

I'd expect an output like in col "p" and "q"

CodePudding user response：

if you have Excel 365 you can use this formula:

=LET(dCheck,$A$2:$A$12,
dResult;$B$2:$B$12;
lookFor, TEXTSPLIT(C1,","),
searchResult,BYROW(dCheck, LAMBDA(r,ISNUMBER(SUM(SEARCH(lookFor,r))))),
FILTER(dResult,searchResult))