Find similar text in Microsoft Excel columns
Introduction
The previous article talked about setting up filters in your spreadsheet, and other articles talk about copying the list into a spreadsheet workbook. However, if you are cataloguing files using Filecats Standard or Filecats Professional, you don’t need to do this – the filters are automatically set up.
Now that you have the filters set up, this article answers this question – how do you actually use them? In this article, we’ll look at filtering for text.
Just as a reminder, filtering is about temporarily removing from view rows which does not full your criteria. For example, you may only want to see orders created last month, or products whose description includes the word “Bike”.
Using the check boxes
If you click on a filter which contains text, you will see a drop down menu with up to 10,000 unique values from the column.
To focus on just one value, click “(Select All)” to deselect all of the checkboxes, and then click the appropriate value. After you have clicked “OK”, only rows which include that criteria will be shown.
If you want to focus on multiple values, then deselect all of the other boxes, either by doing that individually, or by deselecting all of them and reselecting the appropriate values. This is the major advantage of using the check boxes – being able to check multiple values which might not be related to each other.
However, if you have more than 10,000 unique values in the column, you will get a warning of “Not all items showing”. This means that you are not seeing all of the various values, and if you can’t see a value, then you can’t check it (or know of its existence), so beware.
Alternatively, you can click inside the “Search” box and enter what you want. The resulting list will only show those values which include that. For example, if you searched for “bike”, it could show “dirt bike”, “bikes” and “mountain bike (Size: large)”. You can then select which check boxes you want to use as above.
If you already using a filter, using the Search box will also say “Add current selection to filter”. This enables you to combine the results of your previous search with your latest search.
If you create filters in more than one column at a time, then only those rows which fulfil all the requirements will be shown. In this sense, it works as an “AND” instead of an “OR”.
Using a text filter
Instead of using the check boxes, you can also use “Text Filters”. There are 7 different filters:
- Equals – i.e. is identical to,
- Does Not Equal – i.e. is not identical to,
- Begins With – the start of the cell starts with,
- Ends With – the last part of the cell is,
- Contains – somewhere at the beginning, middle or end is,
- Does Not Contains – this is the opposite of the above, and
- Custom Filter.
The Custom Filter allows you to combine two separate filters (so you can say “Begins with N and Ends with ING”), and gives you access to the following:
- Is Greater Than or Equal To. This means that Is Greater Than “S” will return everything starting with “S”, “T” etc.
- Is Greater Than. This is as the above, but will exclude the search term itself.
- Is Less Than,
- Is Less Than or Equal To,
- Does Not Begin With, and
- Does Not End With.
The Custom Filter allows you to do everything that the first 6 options can do, and more. Therefore, unless you have a specific purpose, you should only use the “Custom Filter”.
Additionally, you can use the following “wildcards”:
- ?. The question mark stands for a single character (letter, number, symbol or space), so searching for “BIKE?” will find “BIKER”, “BIKES” and “BIKE9”. It won’t find “BIKERS”, because that would require two additional characters, and it won’t find “BIKE”, because that would require zero additional characters.
- *. The star stands for zero, one or more characters. So “BIKE*” will find “BIKER”, “BIKES”, “BIKE9”, “BIKERS” and “BIKE”. You can use stars at the beginning, end or middle of your criteria.
What’s next
In the next article, we’ll look at filtering on date columns, and filtering for colors.
