searching for a word

**gencoglu27** · 11-06-2011, 09:42 AM

Hi there,
In col "A" There are say 100 words. I need to find all the words that contain any character I enter in the Cell "B1" and list the results in col "D".. For example if I enter "br" in cell B1 I want " bread","hamburger","beer","butter","cucumber"..to be listed in col D . Or if I enter "ea"
I need all the words that contain both "e" and "a" such as in " tea,ice-cream,steak,meat and metalic

Thank you.

**daddylonglegs** · 11-06-2011, 09:51 AM

How many letters do you want to allow in B1?

for 2 letters only you can use this "array formula" in C1

=IFERROR(INDEX(A$1:A$100,SMALL(IF(ISNUMBER(SEARCH(REPLACE(B$1,2,0,"*"),A$1:A$100)),ROW(A$1:A$100)-ROW(A$1)+1),ROWS(C$1:C1))),"")

confirmed with CTRL+SHIFT+ENTER and copied down

**gencoglu27** · 11-08-2011, 04:19 AM

How many letters do you want to allow in B1?]

I think I should allow 3 letters in B1. Then things will be much easier..Could you please modify the code so that I can use 3 letters in B1.

Thanks.

**daddylonglegs** · 11-08-2011, 08:19 AM

Try this version for 1,2 or 3 letters in B1

=IFERROR(INDEX(A$1:A$100,SMALL(IF(ISNUMBER(SEARCH(CHOOSE(LEN(B$1),B$1,REPLACE(B$1,2,0,"*"), REPLACE(REPLACE(B$1,2,0,"*"),4,0,"*")),A$1:A$100)),ROW(A$1:A$100)-ROW(A$1)+1),ROWS(C$1:C1))),"")

confirmed with CTRL+SHIFT+ENTER and copied down