Advanced Character Search Functionality

[Batman1]

Hi Everyone,

If I want to search for letters in the cells that are not necessarily in order, how can I do that?

CEGB	DFACE	EGBDF	FACEG

<tbody>
</tbody>

Example 1
I want to search for any cells that container the letters D,F,E
My results should be cell B1 and Cell C1 because they both container the letters D,F,E even though they are not in sequence.

Example 2
I want to search for any cells that container the letter C
My results should be cell A1, B1 and D1 because they all container the letters C

Is there a simple way of doing this?

 

[aaronbens]

This formula will be blank if A1 does not contain D,F,E and if it does contain it will show Yes
=IFERROR(IF(FIND("D",A1)*FIND("F",A1)*FIND("E",A1)>0,"YES",""),"")

For Example 2
=IFERROR(IF(FIND("C",A1)>0,"YES",""),"")

Note that using the "FIND" function makes the C or the D, F, E are case-sensitive. If you do not want it case sensitive, you can use the "SEARCH" function

 

[Batman1]

This formula will be blank if A1 does not contain D,F,E and if it does contain it will show Yes
=IFERROR(IF(FIND("D",A1)*FIND("F",A1)*FIND("E",A1)>0,"YES",""),"")

For Example 2
=IFERROR(IF(FIND("C",A1)>0,"YES",""),"")

Note that using the "FIND" function makes the C or the D, F, E are case-sensitive. If you do not want it case sensitive, you can use the "SEARCH" function

I am trying to search among almost 1000 cells, the method you proposed would be very time consuming since you have to search each cell individually. I would need a lost of displayed cells that have the letters I am searching.

 

[aaronbens]

What is your expected outcome? How many different letter groups are you hoping to search for?

 

[Batman1]

What is your expected outcome? How many different letter groups are you hoping to search for?

Hundreds of cells, I was going to use this to identify musical chords, but I found a website that does that already.