Get a list of words consisting of certain letters only, from a named range

Still using Excel 2016?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

And your Excel version?

With 365 or 2021:

Formula:

Please Login or Register  to view this content.

With your example

Formula:

Please Login or Register  to view this content.

Please update your profile.

Nice idea, Trevor!

This seems to work:

=LET(a,A1:A12,s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a,B1,""),B2,""),B3,""),FILTER(a,s=""))

Updated 365

Please update your profile.

PLEASE UPDATE YOUR PROFILE!!!

Don't ignore instructions, please.

As far as I can tell, BYROW is BYROW in Norwegian:

=LET(a;A1:A12;FILTRER(a;BYROW(a;LAMBDA(r;BYTT.UT(BYTT.UT(BYTT.UT(r;$B$1;"");$B$2;"");$B$3;"")))=""))

https://support.microsoft.com/nb-no/...0-a4602f2602bb

And what about post #9?

=LET(a;A1:A12;s;BYTT.UT(BYTT.UT(BYTT.UT(a;B1;"");B2;"");B3;"");FILTRER(a;s=""))

This Excel 365 formula does also the job:

Formula:

Please Login or Register  to view this content.

For translation of the formula in your own language, see attached workbook.

Please update your forum profile - I have asked many times already and it still says Excel 2016 ...

Thank you.

There are NO results to return for a, n and k - what were you expecting?

This will work for a, n and c:

=LET(a,VSTACK(A1:A12,B1:B12),s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a,C1,""),C2,""),C3,""),FILTER(a,s=""))

An error trap will show when there are no results:

=IFERROR(LET(a,VSTACK(A1:A12,B1:B12),s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a,C1,""),C2,""),C3,""),FILTER(a,s="")),"No Results")

I assume you are using Excel 365, because you posted in post #6: "Update 365"
Is that right?
You can check yout current version in Excel: Click on your account name (at the top) and click "office user info".

Does the formula work in the sheet I uploaded in Post #12?
And does it still work after you changed something in the input?
Is so, please try that formula.

O I see, you have structured your sheet in another way, the words are now in 2 different columns:

Please try

Formula:

Please Login or Register  to view this content.

A little bit neater, please try:

Formula:

Please Login or Register  to view this content.

@Jec, your formula returns in this example also ancs.

ancs is not correct, because the OP asks: "These words should consist of certain letters only."

The certain letters are a, n & c, but acns contains also a s.

@Hans - you'd posted in the wrong thread. I've moved your post here.

@Tresfjording

Where are we with this? Have you managed to get anybody's suggestions working? Some feedback would be nice.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

The solution in post 9 did the trick.

Thanks for yout feedback.

Strange that you choose formula 9. This formula stopped working in Post #13 when it turned out that your input structure was different.
Subsequently, three members came up with other solutions that fit the new structure.
Was that for nothing?

Furthermore, the formulas in item 17 and item 23 are easier to adapt when the number of different characters of which the selected words must consist becomes more or less.

I think he probably meant post #15, where I addressed the change in layout.

And the OP did say:

And thanks to the rest of you contributng, and making her solution even better.

so I don't read this as anyone else's efforts having been for nothing.

Glad to have helped, anyway.