Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Is it possible to build a word counter in google sheets? So for example if I have a…

  1. User

    Is it possible to build a word counter in google sheets? So for example if I have a sentence like "Mary has many mary days" and I want my detector to detect how many mary's are in the sentence, is it possible withing google sheets? "Mary has many mary days" is just an example

  2. Assistant

    Yes, you can absolutely build a word counter in Google Sheets to detect and count specific words within a sentence. Below, I'll guide you through a step-by-step process to count occurrences of a word (e.g., "mary") in a given sentence, handling case sensitivity and ensuring accurate counts.

    Method 1: Using LEN and SUBSTITUTE Functions

    This method calculates the difference in length between the original text and the text after removing the target word, then divides by the length of the target word to get the count.

    Step-by-Step Guide:

    1. Enter Your Data:

      • Suppose your sentence is in cell A1:
        A1: Mary has many mary days
    2. Choose a Target Word:

      • Let's say you want to count occurrences of "mary" (case-insensitive).
    3. Use the Following Formula:

      =(LEN(LOWER(A1)) - LEN(SUBSTITUTE(LOWER(A1), "mary", ""))) / LEN("mary")
      
      • Explanation:
        • LOWER(A1): Converts the entire text to lowercase to ensure case-insensitive matching.
        • SUBSTITUTE(LOWER(A1), "mary", ""): Removes all instances of "mary" from the text.
        • LEN(...): Calculates the length of the original and modified texts.
        • The difference in lengths divided by the length of "mary" gives the total count.
    4. Result:

      • For A1: Mary has many mary days, the formula returns 2.

    Example:

    AB
    Mary has many mary days=(LEN(LOWER(A1)) - LEN(SUBSTITUTE(LOWER(A1), "mary", ""))) / LEN("mary") → 2

    Method 2: Using REGEXMATCH with SPLIT and COUNTIF

    This method splits the sentence into individual words and then counts how many times the target word appears.

    Step-by-Step Guide:

    1. Enter Your Data:

      • A1: Mary has many mary days
    2. Use the Following Formula:

      =COUNTIF(SPLIT(LOWER(A1), " "), "mary")
      
      • Explanation:
        • LOWER(A1): Converts the text to lowercase.
        • SPLIT(..., " "): Splits the text into an array of words based on spaces.
        • COUNTIF(..., "mary"): Counts how many times "mary" appears in the array.
    3. Handle Punctuation (Optional):

      • If your text contains punctuation, you can use REGEXREPLACE to remove it:
        =COUNTIF(SPLIT(REGEXREPLACE(LOWER(A1), "[^\w\s]", ""), " "), "mary")
        
        • REGEXREPLACE(LOWER(A1), "[^\w\s]", ""): Removes any non-word characters except spaces.
    4. Result:

      • For A1: Mary has many mary days, the formula returns 2.

    Example:

    AB
    Mary has many mary days=COUNTIF(SPLIT(LOWER(A1), " "), "mary") → 2

    Method 3: Using ARRAYFORMULA with REGEXMATCH

    For more advanced scenarios or to handle multiple target words, you can use ARRAYFORMULA with REGEXMATCH.

    Step-by-Step Guide:

    1. Enter Your Data:

      • A1: Mary has many mary days
    2. Use the Following Formula:

      =ARRAYFORMULA(SUM(REGEXMATCH(SPLIT(LOWER(A1), " "), "mary")))
      
      • Explanation:
        • SPLIT(LOWER(A1), " "): Splits the text into words.
        • REGEXMATCH(..., "mary"): Checks each word for "mary".
        • ARRAYFORMULA(...): Processes the array of TRUE/FALSE values.
        • SUM(...): Adds up the TRUE values (each TRUE counts as 1).
    3. Result:

      • For A1: Mary has many mary days, the formula returns 2.

    Example:

    AB
    Mary has many mary days=ARRAYFORMULA(SUM(REGEXMATCH(SPLIT(LOWER(A1), " "), "mary"))) → 2

    Additional Tips:

    • Dynamic Target Word:

      • If you want to make the target word dynamic (e.g., located in cell B1), adjust the formulas accordingly:
        =(LEN(LOWER(A1)) - LEN(SUBSTITUTE(LOWER(A1), LOWER(B1), ""))) / LEN(B1)
        
    • Handling Plurals or Variations:

      • To count variations like "Mary's" or "Marys", you might need to adjust the regex pattern:
        =ARRAYFORMULA(SUM(REGEXMATCH(SPLIT(REGEXREPLACE(LOWER(A1), "[^\w\s]", ""), " "), "^marys?$")))
        
        • This pattern ^marys?$ matches "mary" and "marys".
    • Case Sensitivity:

      • Converting text to lowercase (or uppercase) ensures that the count is case-insensitive.

    By using these methods, you can effectively count specific words within sentences in Google Sheets. Choose the method that best fits your data structure and complexity requirements.