Re: [libreoffice-users] regular expression for calc

Hi Brian
I wish i could say it did help but unfortunately not. It works with the whole cell contents but 
doesnt seem to pick out a word within the cell. For instance, one of my list has contents "X1 G43". 
Now if I have "X1 G43" in the cell Xn (using your example) i get count 1 returned which is correct; 
but if i have "X1" in cell Xn it returns 0, which is clearly wrong (at least as far as my 
requirements are concerned). And yes, i do have regular expressions enabled in Calc >  Calculate. 
My version is 5.2.2.2 x64 and im using 64bit windows 7.

G.
--------------------------------------------
On Sun, 12/3/17, Brian Barker <b.m.barker@btinternet.com> wrote:

 Subject: Re: [libreoffice-users] regular expression for calc
 To: users@global.libreoffice.org
 Date: Sunday, 12 March, 2017, 5:51
 
 At 15:45 11/03/2017 +0000, Gary
 Collins wrote:
> I have a column each row of which contains a sequence of
 one or more 
> codes separated by a space. In case it will make a
 difference each 
> code consists of a letter (occasionally 2 letters)
 followed by a 
> number of up to 3 digits (and occasionally ends with a
 letter) eg X1 
> Aa12 D7a etc.
>
> I want to obtain a regular expression for COUNTIF that
 will enable 
> me to find out how many times a particular code,
 contained in 
> another cell, occurs in the column. I was thinking that
 \b to detect 
> the word boundaries should help but i cant quite get it
 to work; im 
> not sure if this is because it is assuming a word
 boundary between 
> the letter and the number or if it is simply my
 incompetence as 
> usual. Does anyone have an idea what may be the best way
 to achieve this?
 
 Try
 =COUNTIF(range;"\b"&Xn&"\b")
 - where "range" represents the column of data and Xn
 represents the 
 cell containing the code to be counted. Remember that you
 need to 
 have Tools | Options... | LibreOffice Calc | Calculate |
 Enable 
 regular expressions in formulae ticked (which is the case by
 default).
 
 I trust this helps.
 
 Brian Barker
 
 
 -- 
 To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
 Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
 Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
 List archive: http://listarchives.libreoffice.org/global/users/
 All messages sent to this list will be publicly archived and
 cannot be deleted
 
 

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted