How to count if cell contains * in end of words in Google Sheets

user_14831308 0
Viewed 314

In Google Sheets, I have a scenario in A2= Mahe12* Now I need to count the cell if cell ends with *

I tried countif(A2,"*") but no luck.

google-sheets-formula google-sheets regex
3 Answers

Try the following

=COUNT(INDEX(IF(LEN(REGEXEXTRACT(C2:C11,"\*$"))<>1,,1)))

enter image description here

user_15277800

use:

=INDEX(SUM(REGEXMATCH(A2:A; "\*$")))
user_56326290

There is no need to classify/tag this as a regular expression. COUNTIF() among other functions do support wildcards with the asterisk being one of them.

From the docs:

"A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character"

Though poorly documented in the COUNTIF() section, the mechanics are the same as say; VLOOKUP(). Under "Notes" it shows that you can simply escape this special meaning using a tilde:

=COUNTIF(A2:A,"*~*")
  • * - Meaning 0+ characters.
  • ~* - A literal asterisk.

enter image description here

user_97581940
Related