Using regular expressions in OpenOffice Calc filters -- phrases like "contains" rather than equals, less than, etc.

Life just isn't cut and dried. Sometimes when you're filtering you don't want to just say "give me all the people whose last name is Hanson." You want Hanson, Hansen, and Hansengaaardennn (those Dutch really go for the jawbreaker names).

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

Here's how to do that. Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field. Click More, and select Regular Expressions, 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.* (you can also skip the ^, I've found)
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's the data.

Select all  the data, or just click in the headings, and choose Data > Filter > Standard Filter. Make the window look like this.

Click OK and you get this; Bob Montrahans is not included. (It's not because of the case.)

Here's a different example. I want names that DON'T CONTAIN the series of letters Hans.

Data

The window with the restrictions:

And the results.

Here's some information from the OOo wiki about regular expressions.

---

---

Comments

[tower defense]

Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field.

[Adam]

Thank you so much, this is the only page I've found that explains this. Is there any easier way to do it now with OpenOffice 3?

[Solveig]

Hi Adam,

I'm afraid not, but it does get easier.

[JohnB]

Thanx, this came in handy today.