Forum Discussion
Peter_Dankmeijer
Jan 06, 2019Copper Contributor
Counting words in cells with multiple words
In a survey analysis in Excel, I am trying to count unique words in cells with multiple text values per cell. I found the formula =SOM(LENGTE(A2:A7)-LENGTE(SUBSTITUEREN(A2:A7;"apple";"")))/LENGTE("apple") (example to select "apple" in a range of fruit names) but when I press Crtl-Shift-Enter, nothing happens and the formula gives a #value mistake.
In my own research file, I uswed the variation =SOM(LENGTE(GD10:GD10000)-LENGTE(SUBSTITUEREN(GD10:GD10000;"HAVO";""))/LENGTE("HAVO")) (counting the word "HAVO", but I get a value of 83 in stead of the 5 times this word appears in my test file.
Can someone explain how to get a correct result?
- zahidwonderdiCopper Contributor
I found this Excel Word Counter by accident, and it’s a gem! Just upload your file, and it takes care of the word count for you. Easy and efficient. https://pdfwordcounter.io/excel-word-counter/
Hi Peter,
First, I'd use SUMPRODUCT to avoid array formulas. More about this is here https://exceljet.net/formula/count-specific-words-in-a-range
Second, check brackets in your formulas.
Third, take into account SUBSTITUTE is case sensitive.
So, I'd use
=SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER("apple"),"")))/LEN("apple")) and =SUMPRODUCT((LEN(GD10:GD10000)-LEN(SUBSTITUTE(UPPER(GD10:GD10000),UPPER("HAVO"),"")))/LEN("HAVO"))
Above formulas are for English SKU, you may translate them back to Dutch here https://en.excel-translator.de/translator/