Count Instances of a Word from a sentence in a cell

[rakeshseeram]

I have a data  which has a column containing comments entered by various approvers with their email ids. I want to count the no of times an email id is present in each cell. Example in the below image i want to count the no of times XXX@123.com is mentioned in each cell. I want the values to be mentioned as shown in Count column

[BenMoss]

You can use the REGEX_CountMatches(String, pattern) function for this, in your case...

REGEX_CountMatches([Comment],"XXX@123\.com")

Important to escape special characters using a backslash like I have above with the decimal place.

Ben

[Jonathan-Sherman · Accepted solution]

Hi @rakeshseeram,

You could use a regex function to solve this:

REGEX_CountMatches([Comment], 'XXX@123\.com')

If this solves your issue please mark the answer as correct, if not let me know! I've attached a workflow for you to download if needed.

Turns out @BenMoss had the idea of the exact same solution slightly faster than me!

Jonathan

[Joma]

Hey there,

I have a question. Do you have any idea how to create a list of all words in the string or better in the column and its count.

So, I am not searching for a specific word like in this context, but rather want to know all words and for each words how often they occur.

Best

[Bluebird_Tim]

Hi @Joma  - was searching for this exact thing, think I came up with something easy if you don't know regex.

First whatever column you want to do this for, drop all the other columns with a select tool so that you only have the column you want to analyze as the output

Second, Use a text to columns tool and split rows with the delimiter being a space (you can literally hit the space bar or \s either will work).  On the output there should be only 1 in each row

Lastly, use a summarize tool and do a group by and then a count.  The output should have the word in the first column and the second should have the count (frequency) of that word.  If you are on the amp engine you will want to sort afterwards.  

Hope the above is helpful let me know if you have questions!