regular expression for calc

classic Classic list List threaded Threaded
7 messages Options
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|

regular expression for calc

Apologies if this appears twice; im not sure if there was a problem with sending...

Hello,
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 (ooccasionally 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?

Thanks
Gary

--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

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: [hidden email]
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

Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

In reply to this post by Gary Collins
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 <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] regular expression for calc
 To: [hidden email]
 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: [hidden email]
 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: [hidden email]
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
Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

Hi Gary,

find the option "Search criteria = and <> must apply to whole cells" in
Tools > Options > Calc > Calculate. Try it with disabled option.

Kind regards
Regina

Gary Collins schrieb:
> 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.


--
To unsubscribe e-mail to: [hidden email]
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
Bruce Hohl Bruce Hohl
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

If the code you are looking for only occurs once per cell you could use
find() or search() against each cells to determine if the code is present
then countif() the results where greater than zero.

On Sun, Mar 12, 2017 at 1:50 PM, Regina Henschel <[hidden email]>
wrote:

> Hi Gary,
>
> find the option "Search criteria = and <> must apply to whole cells" in
> Tools > Options > Calc > Calculate. Try it with disabled option.
>
> Kind regards
> Regina
>
> Gary Collins schrieb:
>
>> 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.
>>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
> ubscribe/
> 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: [hidden email]
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
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

In reply to this post by Gary Collins
Hi Regina,
Thank you, yes, that's cracked it. (*so* many pitfalls for the unwary!)

Best
Gary
--------------------------------------------
On Sun, 12/3/17, Regina Henschel <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] regular expression for calc
 To: [hidden email]
 Date: Sunday, 12 March, 2017, 17:50
 
 Hi Gary,
 
 find the option "Search
 criteria = and <> must apply to whole cells" in
 
 Tools > Options > Calc >
 Calculate. Try it with disabled option.
 
 Kind regards
 Regina
 
 Gary Collins schrieb:
 > 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.
 
 
 --
 To unsubscribe e-mail to:
 [hidden email]
 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: [hidden email]
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
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: regular expression for calc

In reply to this post by Bruce Hohl
Did the OP get this working.
I have it working with
=COUNTIF(SEARCH("\b"&$G$37&"\b",E37:E39),">1")

$G$37 contains the search term with word boundaries.
E37:E39 is the range I am searching.

Enter into the cell where you want the answer
=COUNTIF(SEARCH("\b"&$G$37&"\b",E37:E39),">1") and press CTL + SFT + Enter

Steve

On 2017-03-13 13:46, Bruce Hohl wrote:

> If the code you are looking for only occurs once per cell you could use
> find() or search() against each cells to determine if the code is present
> then countif() the results where greater than zero.
>
> On Sun, Mar 12, 2017 at 1:50 PM, Regina Henschel <[hidden email]>
> wrote:
>
>> Hi Gary,
>>
>> find the option "Search criteria = and <> must apply to whole cells" in
>> Tools > Options > Calc > Calculate. Try it with disabled option.
>>
>> Kind regards
>> Regina
>>
>> Gary Collins schrieb:
>>
>>> 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.
>>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>> ubscribe/
>> 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: [hidden email]
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