Talk:Documentation/How Tos/Regular Expressions in Calc
Versioning of regex howto in future
Something to ponder before it becomes necessary to deal with it: In the known future is the backreference patch. In the less known future is the move to a new regex engine. I've seen other help pages on the ooowiki that cover multiple versions of OOo, and regularly, although not frequently, see help requests on forums regarding problems in surprisingly old versions (still in use sometimes out of preference, sometimes necessity). When necessary, should this page have multiple entries, one for each variation of regex? Or should it fork, leaving the main page representing only the current version? I'd go for a fork, but maybe others will differ, or maybe there are ooowiki rules about this. --Hgreenhough 10:03, 7 December 2007 (CET)
Regular expressions in Calc functions
- 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.
- 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)
A case insensitivity workaround from drensink
If you need to compare case sensitive substrings rather than the entire string
use =ISNUMBER(FIND("S";A1) or =ISNUMBER(FIND("s";A1) in Cell B1 then copy and
paste the formula into Cells B2 to B200 so you have TRUE or FALSE in column B.
Then use =COUNTIF(B1:B200;1) to count the number of TRUEs.
from: http://qa.openoffice.org/issues/show_bug.cgi?id=75987
--Hgreenhough 15:10, 27 November 2007 (CET)
