Tip for filtering OpenOffice spreadsheet results in a data pilot table
By drapeau on Jul 07, 2009
Here's what I'm trying to do: I have a spreadsheet with data I've gotten from a table in a web page; the table happens to be data from a twiki that our organization uses to track the projects we do. I'm looking at data trends, doing so by creating a data pilot table (in Excel land, a pivot table) of my spreadsheet data. The results are nice, but now I want to see the same results without all the entries that have the string "SSE" in the keywords column of my spreadsheet (many columns in this spreadsheet, one of them contains keywords for the projects we track).
Creating the data pilot table is easy. Creating the filter is easy (on the data pilot, click on the "Filter" button to bring up a dialog that lets you choose your filter parameters). Getting to the regular expression part is easy (in the Filter dialog, click on the button labeled "More >>" and check the box labeled "Regular expression"). But what to type in the "Value" text field? I tried typing \*SSE\*, but that didn't do anything.
I found the solution in this great blog about OpenOffice tips. The solution: type a period before each asterisk, i.e., .\*SSE.\*, instead of \*SSE\* as Excel does it.
It worked beautifully; whew!
Powered by ScribeFire.