I’m trying to write a formula that will count the number of labels in a dropdown column, if any of the labels selected for that item contain “xx” in them. The dropdown allows multiple choices. If any of the labels selected have “xx” in them, I want to count the number of labels.
This is the best I can do, but it doesn’t work.
IF(SEARCH(“xx”,{column#Labels}),COUNT({column#Labels})
Hi Dean,
Here is how to do it. You need to calculate the difference of length of the column value before and after you remove the string of characters you’re searching for.
For instance: your column contains: “United States” (length is 13). Remove the t’s and you get a length of 10. Calculate the difference (13-10 => 3): you have 3 t’s.
To remove a letter/string you use, SUBSTITUTE:
SUBSTITUTE("United States","t","")
To calculate the length of a column content/string:
LEN({Column})
So if we put all this together:
LEN({Column})-LEN(SUBSTITUTE({Column},"t","")
Now this works because “t” is a single letter. But if you’re searching for “xx”, you need to take into account that “xx” has 2 characters, not 1. So if you remove 1 occurrence of “xx”, the difference between the 2 lengths will be 2, not 1.
Remove “te” from “United States”, the resulting string will have a length of 9 (because you removed 4 characters (“te” twice): so you need to calculate the difference 13-9 = 4 and divide it by 2 (the length of the string you removed).
Here is your final formula (replace “xx” by your actual search):
DIVIDE(LEN({Column})-LEN(SUBSTITUTE({Column},"xx","")),LEN("xx"))
Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.
Wow… perfect.
Thanks!