search for cells containing only certain letters

Hi ...

I have treid with Filter, xlookup, search and much much more, but ...

D1 is where the wanted formula will reside.

Column A1:A3 contains a list of words.
C1:C3 contains one letter each.

How du I get a list of words from column A1:A3 containg only of the letters in C1:C7
Same letter can be used more than once.

eks.:
Column A
train
cart
plane
aeroplane
streetcar
tram
art

C1:C3
a
r
t

Output with spill in D1:
train
cart
plane
aeroplane
streetcar
tram
art

I tried =FILTRER(A1:A7,ISNUMBER(SEARCH(C1&"*",A1:A7))) in D1, but it does not give the correct answer.
It lists all words containg these letters and others, not words containing of only these letters.

All I want is the last word art.

VBA?

Please try

=FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,A1:A7)&"</m></x>",CONCAT("//m","[contains(.,'"&C1:C3&"')]"))

You can use a helper column to first determine if a cell satisfies the condition. For example use this formula in B1 (copied down) -

Please Login or Register  to view this content.

This will return TRUE if the word satisfies the condition, else FALSE. Then you can use your Filter function to get the required values.

You should be able to use FILTER function now using the helper column B to filter the required values...

I am using an older version, but this should work in D1 (given that you're using the helper function I provided earlier)-

Please Login or Register  to view this content.

Originally Posted by Tresfjording

I'm looking for words that consist of ONLY a, r, and t.

That is what my formula is doing. It should return TRUE for only these words-

Please Login or Register  to view this content.

Is any of these words invalid?

Originally Posted by Tresfjording

I'm looking for words that consist of ONLY a, r, and t.

Last try

=LET(z,A1:A7000,x,C1:C3,l,LEN(z),r,ROWS(x),n,ROW(x)^0,m,l-LEN(SUBSTITUTE(LOWER(z),TRANSPOSE(x),)),FILTER(z,(MMULT(--(m>0),n)=r)*(MMULT(m,n)=l)))

Originally Posted by Bo_Ry

=LET(z,A1:A7000,x,C1:C3,l,LEN(z),r,ROWS(x),n,ROW(x)^0,m,l-LEN(SUBSTITUTE(LOWER(z),TRANSPOSE(x),)),FILTER(z,(MMULT(--(m>0),n)=r)*(MMULT(m,n)=l)))

That's really cool! (Reminds me that I need to upgrade to Office 365 :p) Although I think OP wanted all words which are entirely made up of those characters but it is not necessary that all of those characters must exist in the word (for example "Ar" should be a valid word). If that's the case, would this be correct?

Please Login or Register  to view this content.

You might want to use a VBA solution in that case (if you're facing memory issues) -

Please Login or Register  to view this content.