Talk:Documentation/How Tos/Regular Expressions in Calc

From Apache OpenOffice Wiki
Jump to: navigation, search

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)


OK - wise words :).

Did you see on the Writer Regex HowTo talk page, that OOo3.0 Help has links to these HowTos? It does mean that a user of an old version will land on the HowTo for the current version - but I still agree with you that keeping one version up to date is enough.

--Drking 8:30, 24 January 2008 (GMT)


I did see that. I assume that means the links are in the installed help, which is accessed from within the program. In theory a good idea, but it does depend on the quality of the Wiki. I wonder what Sun will do with StarOffice - will they include the links and monitor the Wiki quality to protect their corporate image? I know Help is never perfect, but Wikis can get hopelessly out of date, or even vandalised.

--Hgreenhough 10:50, 24 January 2008 (CET)


Help is going online

It's planned to put the whole of the Help online too:

Documentation/Dashboard/Wiki_Help

A monster project, which needs very careful thinking through, but potentially very powerful if done right.

I'm currently re-writing the entire Calc function Help in the Wiki, to give it a more pro look&feel (the original was done in a hurry many years ago, and no-one's had the time to backtrack).

Documentation/How_Tos/Calc:_Functions_listed_by_category

Another monster project. Once more of it is complete I'll give it a link in the main HowTo index. I don't suppose I could tempt you to contribute here too? :)

--Drking 8:00, 26 January 2008 (GMT)


I hope they monitor Wiki Help closely, for quality and vandalism - its importance will make it vulnerable.

I will take a look at the Functions categorisation when I have time, but I can't see me being able to do more than the odd tweak, sorry, Good luck, though.

--Hgreenhough 10:18, 28 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)

or

=SUMPRODUCT(ISNUMBER(FIND("S";A1:A200))) in a single cell

Not sure if this usually fits in a regex HowTo though - as it's a workaround for a simple substring search and can't cope with anything more complex...?

--drking 05:30, 28 June 2008 (BST)

Personal tools