Counting multiple words in a single cell

0

I am looking for a formula that will count specific words in a cell.  The words in the cell I seperated by a comma and there are multiple words in a cell.  I have a single column of approx. 1500 cells.

Thanks in advance

Answer
Discuss

Answers

0
Selected Answer

Bring info into excel, then in Column G put in formula 

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
  ~ which reads info from Column A and counts the number of "," (commas and Adds 1 - for the word after the ",")   OR import into excel and use the "," as a separator ~ this will put each word in it's own cell....then go off past the last filled column for entire imported text and use the Count formula to count the words (actually =Counta to count strings)
Discuss

Discussion

Appreciate the response it works for me.  Actually both suggestions work since another of my goals was to count the words.  I thought I had to put them in a single column but the alternate approach works for me.  
Charlie24 (rep: 2) Dec 13, '19 at 4:15 pm
Add to Discussion
0

You may be able to use COUNTIF with wild cards for that.

=COUNTIF(A:A, "*your word*")

There are some drawbacks. 

  1. The search for "*arm*" will count both "farm" and "garment".
    You might prevent this by constructing your data like ",nix,garment,armament,farm," and search for "*,arm,*"
  2. The formula counts cells in which the search criterium is found. It doesn't count the number of occurrences of the search criterium. Therefore, if the criterium is contained in a cell more than once the count won't reflect that.

If the second issue is your problem you may have to use a UDF (User Defined Function) and VBA, or helper columns, like @queue suggests.

The first problem usually throws up issues about leading and trailing commas and invening spaces. If the data are machine-produced (and therefore don't contain random errors a human might introduce) you might get your result by bearing in mind that you can use ? as a while card, too (it represents a single character). You can also do several counts within one formula and combine them with plus or minus, or select the larger or smaller of the two using MAX() or MIN() function. Personally, I would still prefer a UDF but that depends on the nature of your data as much as personal preference.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login