Count how many times an item appear in a text list (not ref list)

There is no quick and easy way to do this. It would be significantly faster and more efficient to do with with Refs and SELECTs.

1 Like

Interesting, this would only work on string items length 1 though right?


Depending on the complexity of the individual items within the list, data loss could occur because of smashing all the letters together and then accidentally substituting from the combined string.

Edit: Well, not data loss. Just incorrect results.

I’m just in line with his query not assuming complex case.

This can be done using EXTRACTMENTIONS() if you add an “@” to the beginning of your pattern.

COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Pattern","@Pattern ")))

Screen Shot 2020-07-16 at 5.35.15 PM


I was staring at these while writing my first reply thinking “if only there were a way to use extract here somehow”.

Now, does it work if there are spaces in the pattern? Or would the spaces need to get subbed out?

You can search for a pattern with a space, and then just replace it with an underscore so the EXTRACTMENTIONS() part will work.
COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Patt ern","@Patt_ern ")))

Keep in mind, we’re not actually writing the substitute back to the sheet. Just changing the pattern to count it.


That works. Thanks.

Some kind of counting an item from a list should be build in expression. :frowning:

1 Like

Ok. Yeah. That’s what I was referring to. You have to sub the space in the pattern one way or another for it to work.

Edit: or, rather, what I mean is, if your data has, or might have, spaces in it, you need to account for them in some way in regards to using this extractmentions method.

1 Like

It looks like some guy name Patt Ern is getting notifications. :joy:
Screen Shot 2020-07-16 at 5.52.19 PM

The forum made it bold and tagged it lol.

1 Like

There is a bit simper solution:


If “SEARCH_TEXT” is not in the list, the split will still return one item list. So ‘-1’

** Assuming that 'SEARCH_TEXT" must be unique. If any longer text having ‘SEARCH_TEXT’ as sub-string, this solution will not work.


Since @GreenFlux’s and my solutions are based on SUBSTITUTE, the search text must be unique. Otherwise, it won’t work.

It will work for my particular case.

So I will create a feature request.
Thank you guys for the helps.

1 Like

is the feature request.

      SUBSTITUTE(","&[TextList]&",", ","&[Pattern]&",", ",@"&SUBSTITUTE([Pattern], " ", "_")&",")


I feel like this should work pretty much always.

Edit: updated. All you need to do is include the delimiter in the search string and remember to keep it in the replacement string.

I made this real quick as an example. Test data has a mix items with spaces and not. Example also passes the pen/pencil test case, and last item case.

What happen if [Pattern] is post fix of a longer string?

Example “Something Pattern,Pattern”

Another thing is “Pattern” could be end of the list.

It’s doable but without native support but the solution will be convoluted.

Fixed by appending the delimiter to the end of the [ColumnToSearch].

I am unsure what you mean. Like if list is:
Cat, Cat, Dog, Mouse
Search and Count:
Cat, Dog

In my example, I use a drop down of the list to help you pick what item you want to count to eliminate what I think you’re talking about.

So in my sample app ^, I set up a column as an EnumList - just for easy adding/editing the list.
Another column that uses the list for valid if values to select a pattern, so that you can only count full items in the list - so no partial or fuzzy counting.
A VC to reformat that list into a Text string to work with.
And another VC with a slight change but otherwise the same formula originally posted by @GreenFlux, targeting the VC with the reformatted list.

Works for 1st item, last item, pen vs pencil, several sequential of the pattern, mixes of spaces, and anything else I could think of.

1 Like

Seems like your solution could work for cases I can think of.

Not sure why you have to substitute space with underscore?

It doesn’t have to be an underscore, per se. But in order for extract to pull a mention correctly, each “item” in the list can’t have spaces in it. So you could just remove it a different way like SUB([List], [Pattern], “@howdoyoudogoodsir”) and skip my more complicated sub. But you know, ideas.