Difference between revisions of "Talk:Documentation/How Tos/Regular Expressions in Calc"

Revision as of 00:21, 27 November 2007

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)

I put in what I hope is the complete list of functions where regex work. A couple of them are linked to individual function description pages - in time all the help functions will go onto the wiki (the Help people are working on it), so this is a bit temporary because we don't know exactly what the Help team will be doing. The individual function description pages that exist at present are linked from the Conditional Summation HowTo

--drking 00:15, 27 November 2007 (BST)

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)