How to find the count of a word in a cell

[RForster6]

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?

 

[Rick Rothstein]

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]

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]

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
	A	B	C	D	E	F	G	H
1
2		apple			apple, bananas, apricot, peach, peaches		6	6
3		banana			peach, apples, apple, orange, peach		13
4		peach			peach, apple, apple, bananas
5					dapple,apple,apple,peaches,orange,peach
6					appl, banana, speach, banana
Sheet55

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(LEN(","&SUBSTITUTE(SUBSTITUTE(E2:E6,",",",,")," ","")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE(E2:E6,",",",,")," ","")&",",","&B2&",","")))/LEN(","&B2&",")
G2	G2	=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)))&",")))
G3	G3	=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]

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.