Excel – Using Text Filters

by | Apr 26, 2016

This week I was asked an Excel question that I don’t believe I had ever considered before.
excel_filter_01The 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.
Excel_filter_03
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.
Excel_filter_04As 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.
Excel_filter_05
 
 
 
 
 
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.
You can read all of Mel’s blogs here.
Don’t forget, we can train your team in the art of agile business intelligence at any time!

0 Comments

Trackbacks/Pingbacks

  1. How to export an Excel file to pipe delimited file rather than comma delimited file | OptimalBI - […] Excel – Using Text Filters to find out how to filter based on the structure of information or Barry’s…
Submit a Comment

Your email address will not be published. Required fields are marked *