Using Text Filters in Microsoft Excel

The video guide shows how to apply a basic filter to an Excel column and access the text filter menu to apply a filter. The technique enables you to filter tables for up to two different text patterns in a single column which, with well structured data, is normally sufficient.

Occasionally you will want to filter Excel across multiple columns. In contact lists, there are often additional columns containing alternate addresses. If you filtered both columns for Cambridge addresses, you would filter out every entry without two Cambridge addresses. A non-coded option is to add an extra column to the data that combines the other two addresses and to filter on that. If there were addresses in A2 and B2, you could ensure this third column displays the addresses neatly using the formula '=A2&CHAR(10)&B2' where CHAR(10) adds a line break. You can develop this further to only add the line break where an alternate address appears.

Depending on your purpose, you may be able to fully replace the original address columns with the combined version. You can then apply a standard filter to a single column.

Trickier is the situation where you want to filter data for three different bits of text e.g. USA, United States of America and U.S.A. If it's not feasible to clean the data then the best option is a helper column that contains 1s or 0s depending on whether you want to see the data. If you have a specific set of text you need to filter for then you can do this with a formula. A more general purpose solution would require Visual Basic. When assessing filter conditions programatically, you can also filter for the desired terms across multiple columns. It is one method our experts can use for creating a general purpose search function.