How to find the count of a word in a cell

RForster6

New Member
Joined
Jul 30, 2020
Messages
2
Office Version
  1. 365
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?
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
36,557
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
2
Office Version
  1. 365
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top