Excel Expert Help Please

tincat

My problem: I have a whole long list of products or names, e.g. 1000 all in one column. Some product or name phrases may be 3 or 4 words long, so I cannot just do a sort.

I want to find all instances of the word e.g. yellow

Is there a way to pull all phrases that include the term "yellow" out of the 1000 phrase column and list them in a new column?

Thanks

I've heard that you can do something like this with pivot tables but have never used them. Are they easy to learn? Would learning pivot tables always help to separate a term? If so do you know any good tutorials?

thanks again

MothballsWallet

I've just done a quick Google search for pivot tables - hope there's something in there that'll help - I think the first two links have a tutorial.

Failing that, have you looked at the Microsoft website? I think there are some good tutorials on there too.

splishsplash

I know nothing about pivot tables, but if you open the Find window, enter yellow and select Find All, it'll give you all the cells with yellow in them. If you then keep Control pressed (ctrl) while you left click on all the cells found (in the drop down box of the Find window), you can go to Edit and Copy, then Paste into a new column.

tincat

Thanks splishsplash -that works beautifully. You're a star

f1charlie

Alternatively, select the first row.

Data - Filter - Autofilter.

Go to the column containing the data you want to filter, click on the down arrow and select Custom.

Click on the dropdown, select 'contains' and enter the phrase you wish to search for in the adjacent window.

You will then be presented with all cells which contain the phrase searched for and you can copy those and paste them elsewhere.