Documentation/How Tos/Using Regular Expressions in the Calc Standard Filter

From Apache OpenOffice Wiki
Jump to: navigation, search

Life just is not cut and dried. Sometimes when you are filtering you do not want to just say "give me all the people whose last name is Hanson." You want Hanson, Hansen, and Hansengaaardennn.

You would like to filter out everyone except those whose names contain "Hans".

Here is how to do that.

  1. Select Data > Filter > Standard Filter from the menu.
  2. Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field.
  3. Click More Options, and select Regular Expression, then click OK.


Example of what you want
What to enter in the Condition field
Syntax for what to enter in the Value field
Example of what to enter in the Value field
Begins with Hans
=
^x.*
^Hans.*
Does not begin with Hans
<>
^x.*
^Hans.*
Ends with Hans
=
.*x$
.*Hans$
Contains Hans
=
.*x.*
.*Hans.*
Does not contain Hans
<>
.*x.*
.*Hans.*


Here are some examples. Let's say you want all names that start with Hans, but not all names that simply contain Hans.

Here is the data.
Data range to be sorted
  1. Select all the data, or just click in the headings, and choose Data > Filter > Standard Filter.
  2. Select "Name" under Field name, "=" under Condition and enter "Hans.*" under Value.
  3. Click OK and you get this; Bob Montrahans is not included. (It is not because of the case.)
    Sorted data: names starting with Hans

Here is a different example. I want names that DO NOT CONTAIN the series of letters Hans.

  1. Select all the data, or just click in the headings, and choose Data > Filter > Standard Filter.
  2. Select "Name" under Field name, "<>" under Condition and enter ".*Hans.*" under Value.
  3. Click OK and you get this.
    Sorted data: names without Hans



See Also



This How To comes from Solveig Haugland's Blog

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages