Re: [libreoffice-users] regular expression for calc
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 Henschelwrote: 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: users+unsubscr...@global.libreoffice.org 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: users+unsubscr...@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
Re: [libreoffice-users] regular expression for calc
Hi Regina, Thank you, yes, that's cracked it. (*so* many pitfalls for the unwary!) Best Gary On Sun, 12/3/17, Regina Henschel <rb.hensc...@t-online.de> wrote: Subject: Re: [libreoffice-users] regular expression for calc To: users@global.libreoffice.org 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: users+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] 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 Henschelwrote: > 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: users+unsubscr...@global.libreoffice.org > 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: users+unsubscr...@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
Re: [libreoffice-users] 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: users+unsubscr...@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
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.bar...@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 +, 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"&"\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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] regular expression for calc
At 15:45 11/03/2017 +, 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"&"\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+unsubscr...@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