Forum Discussion

Peter_Dankmeijer's avatar
Peter_Dankmeijer
Copper Contributor
Jan 06, 2019

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?

  • zahidwonderdi's avatar
    zahidwonderdi
    Copper 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/

     

Resources