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

Revision as of 09:39, 22 January 2008

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)

One of the problems with these multiple pages (per version) is that you often find yourself on the wrong one - with a lot of information that is out of date. My first thought is that it would be better to have a really good single document that describes the current version. Then anyone searching will always come up with the right page. One of the strengths of OOo is that it is easy and free to upgrade to the latest and greatest.

So I think a fork (and then another.., and then...) might not help in the long term. But I might not be right...

--Drking 14:00, 18 January 2008 (GMT)

I actually meant fork, or multi-version single page, but I wasn't clear. However, I think your third option of single version, single page (i.e. no legacy help), is the way to go - realistically, keeping one page up to date and accurate is hard enough.

It does though, make it all the more important to fill in the page edit summary description when updating - so that people on old versions can find the latest relevant version in History.

--Hgreenhough 10:39, 22 January 2008 (CET)

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)

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)