Subscribe Bookmark RSS Feed

Count number of occurrences of specific words in a string

agaddis

New Contributor

Joined:

Oct 18, 2017

Hello I'm looking to create a new column in my dataset that counts the number of times a specfic word appears in a string. The words are separated by "*" in each row. 

Example table:

Animal List

1  dog*dog*cat*bird*dog

2  dog*dog*cat*bird*dog*dog

 

 

Output should be

Animal List                                        Dog Count

1  dog*dog*cat*bird*dog                         3

2  dog*dog*cat*bird*dog*dog                  4

 

 

Can provide additional information if needed.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is the formula for counting the number of times "dog" appears in a given column

N Rows( Loc( Words( :Column 1, "*" ), "dog" ) )

agaddis.GIF

Jim
4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is the formula for counting the number of times "dog" appears in a given column

N Rows( Loc( Words( :Column 1, "*" ), "dog" ) )

agaddis.GIF

Jim
agaddis

New Contributor

Joined:

Oct 18, 2017

Thank you!!
agaddis

New Contributor

Joined:

Oct 18, 2017

 How would this work for wildcard words? For example if dog had dog1 and another said dog2? I still would want it to count all of the dog words. 

txnelson

Super User

Joined:

Jun 22, 2012

The following formula will find all items with the string "dog" found in it

wordList=words(st,"*");
count=0;for(i=1,i<=n items(wordList),i++,count=sum(count,contains(wordList[i],"dog")));
count;
Jim