Extract list based on first second and last letter of word

Hi All,

I'm completely new here so first hello and I'm sorry for not contributing before asking for help (not that I have much to contribute, I'm not very good with Excel).

So I have a list of words that all vary in length and I need a way to be able to filter them based on the first, second and last characters in the word.

I.e. I input 1st letter = P, 2nd letter = I, last letter = T and it would bring up all relevant words from the list (pilot, pivot, pinterest etc.).

I would like it to be automatic if possible so I can just type in the letters and the list instantly appears.

Would anyone be any to help me out?

Thanks in advance!

Jack

Welcome to the forum.

We are all here to help, but it would help us if you were to attach a sample workbook - details of how to do this are given in the yellow banner at the top of the screen.

Hope this helps.

Pete

Thanks for your help Pete & Crooza. I have attached the file here so you can see. Is it possible to automatically filter based on the letters as the list is otherwise very long.

Thanks.

Jack

Put this formula in cell B7:

=IF(AND(LEFT(A7,2)=$B$1&$B$2,RIGHT(A7)=$B$3),MAX(B$6:B6)+1,"-")

then copy this down to the bottom of your list of names. A quick way of doing this is to select B7 and then double-click on the Fill Handle, which is the small black square in the bottom right corner of the cursor. Initially, the cells will all contain hyphens (which help to show where the formula is active), but if you put a in B1, n in B2 and a in B3, you will see the numbers 1, 2 and 3 appear down column B, indicating where there is a name that matches all three criteria.

To show the list all bunched up, you could use this formula in D1:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

then copy this down as far as you think you will need it (e.g. to D10). Then you can change any of the 3 letters as you wish, and any matching names will appear in column D, as appropriate.

Hope this helps.

Pete

Hi Pete,

This is brilliant and works perfectly.

One last thing, is it possible to make the second letter non-compulsory (i.e. if there is a letter in the second letter box it counts it but it the cell is blank it brings up all options for letters 1 and the last letter)?

Many Thanks,

Jack

Change the formula in B7 to this:

=IF(AND(LEFT(A7)=$B$1,IF($B$2="",TRUE,MID(A7,2,1)=$B$2),RIGHT(A7)=$B$3),MAX(B$6:B6)+1,"")

then copy it down. Try it with this combination - a, space, y - then put a letter in B2.

Hope this helps.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Perfect - Thanks so much Pete!

Jack

You're welcome, Jack. I hope your first experiences here have been pleasant ones.

Pete