Finding names containing letters

[Buckeroo]

I have a list of names, and I want to find names containing certain letters. But I want it to find the names with the letters in no particular order. My current function is
=FILTER(A1:A50, REGEXMATCH(A1:A50, E20))
With it filtering through the names from A1 to A50, and E20 is where the letters are input. If I put "Z" into the input, it will return with the names "Choronzon, Niyaz, Itzpapalotl, Pazuzu". But if I put "ZN" it will come out with nothing. However, I want it to output "Choronzon, Niyaz". Thanks for the help!

 

[6StringJazzer]

Depends on the syntax you are using. What is going to be in the cell? Just the letters, like
nz
?

 

[Buckeroo]

Depends on the syntax you are using. What is going to be in the cell? Just the letters, like
nz
?

Yes, that is how they will be input.

 

[6StringJazzer]

How many letters can be in the cell?

 

[Buckeroo]

How many letters can be in the cell?

I am not sure that there is a limit in Google sheets, if thats what you mean, but the longest name contains 14 letters.

 

[6StringJazzer]

I mean how many letters would you want to enter to match on. nz has 2. Will it always be 2?

 

[Buckeroo]

I mean how many letters would you want to enter to match on. nz has 2. Will it always be 2?

Anywhere from 1 to 14.

 

[6StringJazzer]

I cannot imagine how to write a simple formula that takes a string of 1-14 letters, and produces a regular expression that will match all of the letters but in any order and any position in the target string. I am not even sure how to do that in a regexp. In Excel I would be doing that using VBA, but am not familiar with Google Sheets scripting language.

 

[Buckeroo]

I cannot imagine how to write a simple formula that takes a string of 1-14 letters, and produces a regular expression that will match all of the letters but in any order and any position in the target string. I am not even sure how to do that in a regexp. In Excel I would be doing that using VBA, but am not familiar with Google Sheets scripting language.

Can you explain how to do that in excel then? My buddy does not have excel, but I do, and I could try to figure something out.

 

[Buckeroo]

I have found the solution formula. Here it is.

=FILTER(A1:A50, REGEXMATCH(A1:A50, MID(E20,1,1))*REGEXMATCH(A1:A50, MID(E20,2,1))*REGEXMATCH(A1:A50, MID(E20,3,1))*REGEXMATCH(A1:A50, MID(E20,4,1))*REGEXMATCH(A1:A50, MID(E20,5,1))*REGEXMATCH(A1:A50, MID(E20,6,1))*REGEXMATCH(A1:A50, MID(E20,7,1))*REGEXMATCH(A1:A50, MID(E20,8,1))*REGEXMATCH(A1:A50, MID(E20,9,1)))

 

[6StringJazzer]

You are checking for 9 letters here but said there could be up to 14.