Hi codanians. I’m wondering how to get a count of the occurrence of a partial string in a Text column where the string could be anywhere in text. Let’s say you have a field or column called Comments and you want to get a count on how many times anyone used the word “hockey” in their comment for the entire table, not just a particular row. The word hockey could be anywhere in the comment. In other software, I would use a wildcard like an asterisk * before and after the string to mean it doesn’t matter where that string is, as long as it is in there. I tried using Contains() and In() formulas but didn’t get anywhere. Any pointers would be appreciated. Thanks.
Hi. There’s probably better way to accomplish it
thisRow.Comments.Split("hockey").FormulaMap(SwitchIf(CurrentValue.IsBlank() or CurrentValue.IsNotBlank(),1)).Count()-1
Hi Breno, thanks for the reply. I’m not sure where you would put this formula. Do I need to make a new column in the table and put this formula into the column?
Hey Bruce,
I came up with this one:
concat(Character(9999),full_text,Character(9999)).split(search_for).Count()-1
where Character(9999) has to be a character you never want to count.
You put this in a column or on the canvas, full_text and search_for are both columns or canvas items.
It always helps if you share a dummy document, that way we can put a sample solution in your doc: we know for sure what you are doing and you know how the solution can be applied.
Greetings, Joost
If you just want to count the occurrence of a single word in a string of text you could do something like this:
You could also use the lower() function to help safeguard against different capitalizations.
If the word is in multiple rows you could do something like this:
And lastly, if you want to find text that might be in the MIDDLE of a word you can do this:
2 Likes
Hi Scott, your solution was absolutely what I was looking for. Perfect. I don’t even need to put that into a column in the table. It can just be a formula outside the table on the canvas. Thanks to you and everybody else who is so generous with their time and knowledge.
1 Like
Thanks Joost for your reply. I ended up going with Scott’s solution below.
I understand - I like Scott’s solution better too 
Hi Scott. Once again, can’t thank you enough for the solution you provided. As much as I can now work with that formula and manipulate it for my own context, there’s a couple of things I don’t understand. One, is why does it add the capital T as a suffix to the field/column Name in the formula and two, I don’t understand the Split(" ") part, why is it there, is it some kind of delimiter declaration? Thanks.
The captial “T” is Codas Chip Value designation. Essentially, every colored chip in a formula is related to an underlying type, of which some possible types are:
- Text
- Rows
- Numbers
- Time
- Dates
And then on top of that you can have data types that are lists of text, list of rows, lists of numbers and so on. So all in all the capital T just helps you write formulas in knowing what kind of data you are drawing out of your tables.
Second, the split(" ") is turning a text value (thats why you see the T) into a list - specifically a list having a unique value for everything in the text column separated by a space (so yes, the " " space is your delimiter to define how the text column will be split up into a list)
Does that help?
Got it. That’s a big help. So if you used split("|") then the list would be created with the pipe character between each bit of text, right?
Splitting text creates a list based off what you define as the split parameter. So if you split on nothing ("") it will create a list where every character in a string is a unique value.
if you did text.split(" ").join("|") then it would place a pipe character between every word.
If you did text.split("").join("|") then it would place a pipe character between every character
1 Like
Wow, Scott, you are an awesome teacher. thank you so much for that excellent explanation. I’m a VB guy so learning the coda syntax is a bit of learning curve. The tutorials from coda don’t go very deep in explaining the programming side of things. You should start a Youtube channel on this stuff. I have an 11 year old son who loves coda but he often asks me to teach him how to do things. In spite of my extensive programming and database background, I usually have to learn the “coda way” before I can teach it to him. If you could give interactive classes to my son I would be interested. Thanks again.
Hey Bruce!
Glad to hear Im a good teacher (because I actually am a teacher!) – I too have zero background in tech and coding. My educational background is in religious studies (I can read ancient greek!) and my day-job is a public high school teacher.
So you can definitely learn it too! Keep pressing on. . .
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.