asked 2012-07-15 23:27:54 +0200

Farmer Bob
17 ● 1 ● 1 ● 4

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

delete close flag offensive retag edit

Comments

Yes, 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.

Farmer Bob ( 2012-07-16 03:45:08 +0200 )edit

5 Answers

Sort by » oldest newest most voted

answered 2012-07-16 00:06:13 +0200

mariosv

4629 ● 20 ● 45

=countif(A1:A12,".*F")

link delete flag offensive edit

Comments

Have you enable regular expression?: Menu/Tools/Options/Calc/Calculate/ Enable regular expressions in formulas.

mariosv ( 2012-07-16 03:11:20 +0200 )edit

I 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".

tohuwawohu ( 2012-07-16 11:13:12 +0200 )edit

the joys with regular expressions is that you can also make it "F followed by optional space" if needed :-)

cloph ( 2012-07-26 17:34:23 +0200 )edit

Pinging @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 ;-)

qubit ( 2013-02-20 05:53:53 +0200 )edit

answered 2013-02-21 05:58:24 +0200

mahfiaz

1611 ● 17 ● 15

You 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")

link delete flag offensive edit

Comments

@mahfiaz: this is a nice solution! It differentiate upper and lower case characters as well as spaces. Well thought through!

ROSt52 ( 2013-02-21 11:47:16 +0200 )edit

answered 2013-02-20 15:29:26 +0200

ROSt52

1734 ● 30 ● 30

updated 2013-02-20 15:30:23 +0200

I 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

link delete flag offensive edit

answered 2013-02-20 11:01:38 +0200

ROSt52

1734 ● 30 ● 30

Could this be a possibility: Use right(cell, lenght(cell)-1) to identify the most right character and then work with countif?

link delete flag offensive edit

-1

answered 2012-07-16 00:30:52 +0200

Farmer Bob
17 ● 1 ● 1 ● 4

Hello

Thanks but that gives a result of 0.

link delete flag offensive edit

Comments

Please don't use the answer functionality for comments.

tohuwawohu ( 2012-07-16 11:13:55 +0200 )edit

I 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.

Farmer Bob ( 2012-07-16 16:34:15 +0200 )edit

Ok, so we need someone who could test this on a Mac. I've only Linux an Windows available, sorry.

tohuwawohu ( 2012-07-16 17:29:19 +0200 )edit

Thanks, I have also tried a workaround using SUMPRODUCT and REPLACE but still get 0.

Farmer Bob ( 2012-07-16 18:20:07 +0200 )edit

Would any one have a suggestion on a SUMPRODUCT & REPLACE formula that might work? Or a different work around?

Farmer Bob ( 2012-07-16 18:22:26 +0200 )edit

Resources

Donate

LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!

Question tools

Stats

Asked: 2012-07-15 23:27:54 +0200

Seen: 736 times

Last updated: Feb 21

Countif based on last character in text string

Comments

5 Answers

Comments

Comments

Comments

Resources

Donate

Question tools

Stats

Related questions