Sometimes, friends ask me how to extract or get rid of some rows from an Excel spreadsheet. I did something similar before but also need some research to figure out the right way. Of course we can use a programming language and regular expression to do this task, but Excel has already provided such functions. Here I am posting to make a memorandum in case of future use.
In my current version Office 2010 (and I believe it's also the case for Office 2007), Excel provides a way to filter data based on customized criteria. Just follow "Data"->"Filter"->"Advanced". For numerical criteria, users can use relational operations in the criteria cell. This posts focuses on text values instead, and there are many options as listed below.
Notes:
The column names should exist and match for data area and criteria area.
Multiple criteria fields can be placed in adjacent columns.
Criteria in the same row means logical AND, and in different rows means logical OR.
* and ? are wild cards for this use and different from their functions in regular expression.
More combinations are possible based on the rules given below.
The column names should exist and match for data area and criteria area.
Multiple criteria fields can be placed in adjacent columns.
Criteria in the same row means logical AND, and in different rows means logical OR.
* and ? are wild cards for this use and different from their functions in regular expression.
More combinations are possible based on the rules given below.
| ="=text" | Select cells whose contents are exactly equal to the string "text" |
| <>text | Select cells whose contents are not equal to the string "text" |
| text | Select cells whose contents begin with the string "text" |
| >text | Select cells whose contents are ordered (alphabetically) after the string "text" |
| *text* | Select cells whose contents contain the string "text" |
| text*text | Select cells whose contents begin with the string "text" AND contain a second occurrance of the string "text" |
| ="=text*text" | Select cells whose contents begin with the string "text" AND end with the string "text" |
| ?text | Select cells whose contents begin with any single character, followed by the string "text" |
| ="=text?text" | Select cells whose contents begin with the string "text" AND end with the string "text" AND contain exactly one character between these two strings |
| ="=???" | Select cells whose contents contain exactly 3 characters |
No comments:
Post a Comment