Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 21 Feb 2013 11:47:16 +0100Countif based on last character in text stringhttps://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/I have a column of text based codes. Some end with F, some with A and some with - .
A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F
I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,*.f)
and
=countif(A1:A12,"*.F")
and
=countif(A1:A12,"=*.F")
but they do not work.
=countif(A1:A12,"W.*") works, returns 1
Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.
Any help would be great.
Thanks
FB
Sun, 15 Jul 2012 23:27:54 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/Comment by Farmer Bob for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3913#post-id-3913Yes, regular expressions are enabled. As a result countit based on the first character works for example =countif(A2:A12,"W.*") returns 1 but countif based on the last character does not.Mon, 16 Jul 2012 03:45:08 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3913#post-id-3913Answer by m.a.riosv for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=3909#post-id-3909=countif(A1:A12,".*F")Mon, 16 Jul 2012 00:06:13 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=3909#post-id-3909Comment by cloph for <p>=countif(A1:A12,".*F")</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=4262#post-id-4262the joys with regular expressions is that you can also make it "F followed by optional space" if needed :-)Thu, 26 Jul 2012 17:34:23 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=4262#post-id-4262Comment by m.a.riosv for <p>=countif(A1:A12,".*F")</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3912#post-id-3912Have you enable regular expression?: Menu/Tools/Options/Calc/Calculate/ Enable regular expressions in formulas.Mon, 16 Jul 2012 03:11:20 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3912#post-id-3912Comment by tohuwawohu for <p>=countif(A1:A12,".*F")</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3916#post-id-3916I can confirm that this formula works fine with regular expressions enabled. Of course, it will fail if the strings have trailing spaces after the last "F".Mon, 16 Jul 2012 11:13:12 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3916#post-id-3916Comment by qubit for <p>=countif(A1:A12,".*F")</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=12621#post-id-12621Pinging @jorendc: Could you please lend your mac-testing skills to this question? Please let us know if mariosv's Answer seems to work. If it acts differently on different OSes, then this question gets a free trip to you-know-where! (no, not Mordor... although it's about as scary to some users ;-)Wed, 20 Feb 2013 05:53:53 +0100https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=12621#post-id-12621Answer by Farmer Bob for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=3910#post-id-3910Hello
Thanks but that gives a result of 0.
FBMon, 16 Jul 2012 00:30:52 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=3910#post-id-3910Comment by Farmer Bob for <p>Hello</p>
<p>Thanks but that gives a result of 0.</p>
<p>FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3931#post-id-3931Thanks, I have also tried a workaround using SUMPRODUCT and REPLACE but still get 0.Mon, 16 Jul 2012 18:20:07 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3931#post-id-3931Comment by tohuwawohu for <p>Hello</p>
<p>Thanks but that gives a result of 0.</p>
<p>FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3928#post-id-3928Ok, so we need someone who could test this on a Mac. I've only Linux an Windows available, sorry.Mon, 16 Jul 2012 17:29:19 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3928#post-id-3928Comment by tohuwawohu for <p>Hello</p>
<p>Thanks but that gives a result of 0.</p>
<p>FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3917#post-id-3917Please don't use the answer functionality for comments.Mon, 16 Jul 2012 11:13:55 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3917#post-id-3917Comment by Farmer Bob for <p>Hello</p>
<p>Thanks but that gives a result of 0.</p>
<p>FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3932#post-id-3932Would any one have a suggestion on a SUMPRODUCT & REPLACE formula that might work? Or a different work around?Mon, 16 Jul 2012 18:22:26 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3932#post-id-3932Comment by Farmer Bob for <p>Hello</p>
<p>Thanks but that gives a result of 0.</p>
<p>FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3926#post-id-3926I have double checked for blank spaces following the last character and also manually entered a sample list on a new sheet and still it returns 0. The same problem exists when I try it with Open Office. I am running OSX Snow Leopard on an iMac. Mon, 16 Jul 2012 16:34:15 +0200https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=3926#post-id-3926Answer by mahfiaz for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12696#post-id-12696You could always create a new column which has only the last character, e.g with this formula: `=UPPER(RIGHT(TRIM(A1);1))`
TRIM() removes leading and trailing whitespace, RIGHT() picks given number of last characters and UPPER() converts everything to UPPERCASE.
How to count certain characters? `=COUNTIF(B1:B9;"F")`Thu, 21 Feb 2013 05:58:24 +0100https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12696#post-id-12696Comment by ROSt52 for <p>You could always create a new column which has only the last character, e.g with this formula: <code>=UPPER(RIGHT(TRIM(A1);1))</code></p>
<p>TRIM() removes leading and trailing whitespace, RIGHT() picks given number of last characters and UPPER() converts everything to UPPERCASE.</p>
<p>How to count certain characters? <code>=COUNTIF(B1:B9;"F")</code></p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=12727#post-id-12727@mahfiaz: this is a nice solution! It differentiate upper and lower case characters as well as spaces. Well thought through!Thu, 21 Feb 2013 11:47:16 +0100https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?comment=12727#post-id-12727Answer by ROSt52 for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12629#post-id-12629Could this be a possibility: Use right(cell, lenght(cell)-1) to identify the most right character and then work with countif?Wed, 20 Feb 2013 11:01:38 +0100https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12629#post-id-12629Answer by ROSt52 for <p>I have a column of text based codes. Some end with F, some with A and some with - .</p>
<p>A1 KnotsP-A
A2 SaucyF--
A3 B-SweP-F
A4 StancF-F
A5 RoothF-F
A6 GrainF--
A7 RavenP-F
A8 GermaP-A
A9 ParkeF-F
A10 LucieFE-
A11 Top MPEF
A12 WarneF-F</p>
<p>I would like to be able to count how many end with F.
I have tried a number of variations suce as:
=countif(A1:A12,<em>.f)
and
=countif(A1:A12,"</em>.F")
and
=countif(A1:A12,"=<em>.F")
but they do not work.
=countif(A1:A12,"W.</em>") works, returns 1</p>
<p>Please note that the formulae include the wildcard asterix but they don't seem to show up on this blog.</p>
<p>Any help would be great.</p>
<p>Thanks
FB</p>
https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12638#post-id-12638I just had a couple of minutes and could make a test. This is the result according to my understanding of the question.
Should this be wrong, I need more information on the task.
![image description](/upfiles/1361370603540815.jpg)
Wed, 20 Feb 2013 15:29:26 +0100https://ask.libreoffice.org/en/question/3907/countif-based-on-last-character-in-text-string/?answer=12638#post-id-12638