# How to find the count of a word in a cell

#### RForster6

##### New Member
Hi all,
I've been searching around and can't quite get a formula to work. I am making a list of keywords for a subject and I want to count the number of times a word is found in a cell. I actually would like to search a range of cells like E:E rather than E2 but I would settle for the latter.
I have a tab for articles and paste values in E2, E3 etc
Keywords tab has my list of keywords like apple, banana, peach etc.
=COUNTIF(articles!E:E,B2) works and gives me a count of the cells that contain my keywords.

As I was working on this I made a formula that worked:
=SUM(LEN(articles!\$E\$2:\$E\$6)-LEN(SUBSTITUTE(Jobs!\$E\$2:\$E\$6,B2,"")))/LEN(B2)

B2 has the word I am looking for. This seems to be explicit to the value in B2. If B2 is "but" will the formula find both but and butter?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Rick Rothstein

##### MrExcel MVP
B2 has the word I am looking for. This seems to be explicit to the value in B2. If B2 is "but" will the formula find both but and butter?
Yes. I cannot think of a formula solution to do this for just the word by itself, but I am pretty sure I can give you a VBA solution if you are interested.

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Keywords tab has my list of keywords like apple, banana, peach etc.

... I made a formula that worked:
=SUM(LEN(articles!\$E\$2:\$E\$6)-LEN(SUBSTITUTE(Jobs!\$E\$2:\$E\$6,B2,"")))/LEN(B2)
Keywords, articles, Jobs ... the multiple tab names are hard to follow.

Any chance of a small set of dummy data and expected results with XL2BB and a fresh explanation in relation to that sample data?

#### Amit Tandon

##### Board Regular
Cells G2 & H2 return the Count of cell B2 occurrences in E2:E6, while cell G3 formula Counts multiple values of B2:B4. Cell H2 formula is case-sensitive but using UPPER can make it case-insensitive also. Count for "apple" does not consider "apples". Note: The count is for comma separated substrings (comma with or without space as separators). Below is displayed for values in the same tab for easy illustration.

26Aug19.xlsx
ABCDEFGH
1
2appleapple, bananas, apricot, peach, peaches66
3bananapeach, apples, apple, orange, peach13
4peachpeach, apple, apple, bananas
5dapple,apple,apple,peaches,orange,peach
6appl, banana, speach, banana
Sheet55
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(LEN(","&SUBSTITUTE(SUBSTITUTE(E2:E6,",",",,")," ","")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE(E2:E6,",",",,")," ","")&",",","&B2&",","")))/LEN(","&B2&",")
G2G2=SUM(--ISNUMBER(SEARCH(","&B2&",",","&TRIM(MID(SUBSTITUTE(E2:E6,",",REPT(" ",LEN(E2:E6))),(TRANSPOSE(ROW(1:20))-1)*LEN(E2:E6)+1,LEN(E2:E6)))&",")))
G3G3=SUM(--ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(E2:E6,",",REPT(" ",LEN(E2:E6))),(TRANSPOSE(ROW(1:20))-1)*LEN(E2:E6)+1,LEN(E2:E6))),B2:B4,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### RForster6

##### New Member
All, Thanks for responding.
Above the formula has jobs in it and should be articles. Was trying the formula in one tab trying data in two different tabs. Sorry.
This formula works: =SUM(LEN(articles!\$E\$2:\$E\$6)-LEN(SUBSTITUTE(articles!\$E\$2:\$E\$6,B3,"")))/LEN(B3)
The word I want a count of is in Column B and the count is in C.

Another way to do this is to take the cell with then text and do Text to Columns. Copy and paste special transpose in a new tab. Repeat for all the cells that have text you want to analyze. Then do a pivot table.

Replies
1
Views
43
Replies
5
Views
173
Replies
5
Views
306
Replies
10
Views
234
Replies
3
Views
141