Difference between revisions of "Talk:Documentation/How Tos/Regular Expressions in Calc"
From Apache OpenOffice Wiki
Hgreenhough (Talk | contribs) m |
Hgreenhough (Talk | contribs) m (another example, and tidied) |
||
| Line 1: | Line 1: | ||
| − | + | == Regular expressions in Calc functions == | |
# The example given will only count ''''Fred'''' and ''''bride'''' <u>if</u> ''"Search criteria = and <> must apply to whole cells"'' is OFF. If it is ON then only ''''red'''' and ''''ROD'''' are counted. | # The example given will only count ''''Fred'''' and ''''bride'''' <u>if</u> ''"Search criteria = and <> must apply to whole cells"'' is OFF. If it is ON then only ''''red'''' and ''''ROD'''' are counted. | ||
| Line 9: | Line 9: | ||
--[[User:Hgreenhough|Hgreenhough]] 16:52, 23 November 2007 (CET) | --[[User:Hgreenhough|Hgreenhough]] 16:52, 23 November 2007 (CET) | ||
| + | == Examples == | ||
| − | Here's | + | 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: |
<pre>Insert > Names > Define... (Ctrl+F3) | <pre>Insert > Names > Define... (Ctrl+F3) | ||
Name: AnyCharOrNone | Name: AnyCharOrNone | ||
| Line 19: | Line 20: | ||
--[[User:Hgreenhough|Hgreenhough]] 12:43, 23 November 2007 (CET) | --[[User:Hgreenhough|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) | ||
| + | <pre>^[^,]+$ | ||
| + | or | ||
| + | ^[^,]*$</pre> | ||
| + | from: http://www.oooforum.org/forum/viewtopic.phtml?p=244506#244506 | ||
| + | |||
| + | --[[User:Hgreenhough|Hgreenhough]] 17:12, 23 November 2007 (CET) | ||
Revision as of 16:12, 23 November 2007
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)
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)
