This week I was asked an Excel question that I don’t believe I had ever considered before.

The scenario being: if you have a list of information held in one column of an excel spreadsheet how do you filter the contents based on the structure of the information?
For our example, we want to filter all the summary headings i.e., following the format “1.2 something interesting”


Add a filter to Column A by selecting cell A1, then goto > Data and select the filter.

Select the filter down arrow menu to show the standard filter options within Excel.
For our particular scenario we will be using a Text Filter, so select “Text Filter”.
As we want to select all available data that contains a particular structure, the select “Contains”.


There are many guidelines for formatting a numbers and text in excel – please see here for full details.

As the information, we are wanting to filter contains the format “1.2 something interesting” I am using the placeholder for a series of characters. This will ensure that if the numbers move into 10’s or 100’s that we still capture them.
So I have entered ” *.* * ” as the descriptor for the information to be filtered.  Select “OK”.


The results are shown.

There are many more complex filters that can be used in Excel – please see here for an overview of some others.
Data to decisions – Mel
Mel blogs about how data is used to make better decisions.