Talk:Documentation/How Tos/Regular Expressions in Calc

Regular expressions in Calc functions

1. The example given will only count 'Fred' and 'bride' if "Search criteria = and <> must apply to whole cells" is OFF. If it is ON then only 'red' and 'ROD' are counted.
2. I know the section is specific to functions, but I was still confused by the paragraph at the end. I think it would be clearer starting "Note that regular expression searches within functions are always case insensitive ...", and the words "also seem to" near the end are, to me, superfluous - the structures are simply case insensitive.

--Hgreenhough 12:46, 8 November 2007 (CET) & 10:06, 12 November 2007 (CET)

Updated image which no longer matched text, and expanded on example.

--Hgreenhough 16:52, 23 November 2007 (CET)

Examples

Here's a clever example from Villeroy - how to use a regexp in COUNTIF (and presumably the other functions which support regexp) when it has to work across Calc and Excel:

Insert > Names > Define... (Ctrl+F3) 
Name: AnyCharOrNone 
Assigned to: IF(ISERROR(SEARCH(".*";"x"));"*";".*") 
[Add], [OK] 
Your formula: =COUNTIF(range; "start"&AnyCharOrNone&"end")

from: http://www.oooforum.org/forum/viewtopic.phtml?t=66093

--Hgreenhough 12:43, 23 November 2007 (CET)

And here's an example from acknak that finds cells not containing a user input comma (i.e. doesn't work if comma is supplied by the cell's formatting)

^[^,]+$
or
^[^,]*$

from: http://www.oooforum.org/forum/viewtopic.phtml?p=244506#244506

--Hgreenhough 17:12, 23 November 2007 (CET)