Reddkryten

Q: Search for frequency in text strings

Hello,


I am working on a project and trying to count how often certain text strings occur in cells.


If the values were numbers, I could use the frequency function, however it doesn't seem to work with text. Is there another function I could use or a way around this?

iPad, iOS 5.1

Posted on Oct 30, 2015 12:58 PM

Close

Q: Search for frequency in text strings

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Nov 1, 2015 1:38 PM in response to Reddkryten
    Level 5 (5,041 points)
    Mac OS X
    Nov 1, 2015 1:38 PM in response to Reddkryten

    Hi Reddkryten,

     

    COUNTIF() could work for you. If there might be other text before or after start or end your search string with a "*" i.e. "thurs*" will find "Thursday".

     

    More info would be helpful. Can you provide a screenshot?

     

    quinn

  • by SGIII,Apple recommended

    SGIII SGIII Nov 8, 2015 6:04 PM in response to Reddkryten
    Level 6 (10,691 points)
    Mac OS X
    Nov 8, 2015 6:04 PM in response to Reddkryten

    Reddkryten wrote:

     

    trying to count how often certain text strings occur in cells.

     

    If you need a count of how often a string occurs in cell(s) and it may occur more than once within a cell, then you can do something like this:

     

    Screen Shot 2015-11-08 at 20.55.16.png

     

    The formula in B2, copied down the column is:

     

       =(LEN(A2)−LEN(SUBSTITUTE(A2,B$1,"")))÷LEN(B$1)

     

    This compares the original length of the source text with the length of the text when the search string is removed from it (by substituting "" or nothing for the search string) and then divides by the length of the search string to derive how often the search string occurs in the source text.

     

    If, on the other hand, you simply need a count of the cells that contain the search string (perhaps more than once) then you can use COUNTIF. In C5 I have  this:

     

    =COUNTIF(A,"*text*")

     

    Note that the last row containing the formulas is defined as a Footer Row, allowing the use of A and B to refer to the entire column in the formulas.

     

    Illustrations are from Numbers for the Mac but the formulas are the same in Numbers for iOS.

     

    SG