Ask Your Question

Countif based on last character in text string [closed]

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

Farmer Bob gravatar image

updated 2012-07-15 23:36:12 +0100

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

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-22 19:41:52.458156


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 gravatar imageFarmer Bob ( 2012-07-16 03:45:08 +0100 )edit

5 Answers

Sort by » oldest newest most voted

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

m.a.riosv gravatar image


edit flag offensive delete link more


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

m.a.riosv gravatar imagem.a.riosv ( 2012-07-16 03:11:20 +0100 )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 gravatar imagetohuwawohu ( 2012-07-16 11:13:12 +0100 )edit

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

cloph gravatar imagecloph ( 2012-07-26 17:34:23 +0100 )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 gravatar imagequbit ( 2013-02-20 05:53:53 +0100 )edit

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

mahfiaz gravatar image

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

edit flag offensive delete link more


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

ROSt52 gravatar imageROSt52 ( 2013-02-21 11:47:16 +0100 )edit

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

ROSt52 gravatar image

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

edit flag offensive delete link more

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

ROSt52 gravatar image

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

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

edit flag offensive delete link more

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

Farmer Bob gravatar image


Thanks but that gives a result of 0.


edit flag offensive delete link more



Please don't use the answer functionality for comments.

tohuwawohu gravatar imagetohuwawohu ( 2012-07-16 11:13:55 +0100 )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 gravatar imageFarmer Bob ( 2012-07-16 16:34:15 +0100 )edit

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

tohuwawohu gravatar imagetohuwawohu ( 2012-07-16 17:29:19 +0100 )edit

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

Farmer Bob gravatar imageFarmer Bob ( 2012-07-16 18:20:07 +0100 )edit

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

Farmer Bob gravatar imageFarmer Bob ( 2012-07-16 18:22:26 +0100 )edit

Question Tools


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

Seen: 9,914 times

Last updated: Feb 21 '13