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]

REGEXMATCH is not a built-in Excel function (at least not in my version of Excel 365). Where are you getting that?

If you can use RegEx for the pattern, instead of ZN use

zn|nz

 

[Buckeroo]

REGEXMATCH is not a built-in Excel function (at least not in my version of Excel 365). Where are you getting that?

If you can use RegEx for the pattern, instead of ZN use

zn|nz

Ah sorry, my friend gave me that message and I just sent it here cause I do not know much. He did not tell me it was used in Google Sheets. Sorry for the inconvenience! Do you have any recommendations on where I could get help for Google Sheets?

 

[Peter_SSs]

Do you have any recommendations on where I could get help for Google Sheets?

I have moved the thread to the forum where Google Sheets questions should be asked.

 

[6StringJazzer]

I am not familiar with Google Sheets so much but I do know regex and my answer should still be valid.

 

[Buckeroo]

I am not familiar with Google Sheets so much but I do know regex and my answer should still be valid.

With your solution, wouldnt it only show names with zn and nz, in that order? The names that are shown need to contain those letters, not in any order.

 

[6StringJazzer]

Your specification is not really very specific. Is "Choronzon, Niyaz" one name (last, first) or two names? It looks like you matched it because of "nz".

I am starting to think that you mean:

"Find all words that contain the letter n and the letter z, anywhere in the word, in any order."

In that case:

z.*n|n.*z

or possibly

(z.*)?n(z.*)?

I suspect you also need a match that is case-insensitive. I do not know how to specify that in Google Sheets.

 

[Buckeroo]

Your specification is not really very specific. Is "Choronzon, Niyaz" one name (last, first) or two names? It looks like you matched it because of "nz".

"Find all words that contain the letter n and the letter z, anywhere in the word, in any order."

I suspect you also need a match that is case-insensitive. I do not know how to specify that in Google Sheets.

Yes, sorry, it is two different names, it does not matter if it is case-sensitive or not. And yes "Find all words that contain the letter n and the letter z, anywhere in the word, in any order." is correct. However the formula you gave me returns an error. "Formula parse error."

 

[6StringJazzer]

Should be a valid regexp expression. I'll have to set up a test in Google Sheets.

 

[Buckeroo]

Should be a valid regexp expression. I'll have to set up a test in Google Sheets.

Yes, it is a valid expression, it did work, my buddy was putting it in wrong . Is there any way to use one of those expressions, but pulling the letters from a cell? Thanks.