CODEWITHSUNDEEP
excelexcel-formula
Liu Kang
Liu Kang

Reputation: 1389

Exact frequency of a specific word in a single cell (excluding suffix and prefix)

I earlier worked out a good solution for this with the help of the comunity, it works really good but I found out it can only handle suffix words (it dosen't ignore prefix-words).

Formula:

=IF(B1<>"";(LEN(A1)-LEN(SUBSTITUTE(A1;B1&" ";"")))/(LEN(B1)+1)+IF(RIGHT(A1;LEN(B1))=B1;1;0);"")

A contains sentences, multiple words (without punctuation)
B contains the word I want to count the exact frequency of.
C here is there the formula is placed and where I get the result

Sample table:

|              A            |  B   |     C    |
|:-------------------------:|:----:|:--------:|
| boots                     | shoe |     0    |
----------------------------------------------|
| shoe                      | shoe |     1    |
----------------------------------------------|
| shoes                     | shoe |     0    |
----------------------------------------------|
| ladyshoe dogshoe catshoe  | shoe |     3    |
----------------------------------------------|

In C-column I am getting correct output in row 1, 2 and 3 but not 4. I want C4 should return 0 and not 3.

The problem is that it makes no match for shoexxxxxxxxxxx (correct) but makes a match for xxxxxxxxxxxshoe (wrong).

I only want the formula to count the exact match for shoe, any other word should not be counted for.

Upvotes: 0

Views: 169

Answers (1)

Mark Butler
Mark Butler

Reputation: 895

You want this formula:

=IF(B1<>"",(LEN(A1)-LEN(SUBSTITUTE(A1," "&B1&" ","")))/(LEN(B1)+2),"")+IF(A1=B1,1,0)+IF(LEFT(A1,LEN(B1)+1)=B1&" ",1,0)+IF(RIGHT(A1,LEN(B1)+1)=" "&B1,1,0)

I'll denote a space by * to make the following clearer:

There are four cases to consider:

  1. string; the word has no spaces on either side (and is therefore the only word in cell A1
  2. string*; the word appears at the start of a list of words.
  3. *string; the word appears at the end of a list of words.
  4. *string*; the word is in the middle of a list of words.

First we count the number of occurrences of *string*, by substituting "*string*" for "", subtracting the length of the new string from the old one, and dividing by len(string)+2 (which is the length of *string*).

Then we add one more to our count if A1 is exactly string, with no spaces either side.

Then we add one more if A1 starts with string*, and one more if A1 ends with *string.

Upvotes: 1

Related Questions