Closed Thread
Results 1 to 2 of 2

Formula to Count the Number of Occurrences of a Text String in a Range

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Formula to Count the Number of Occurrences of a Text String in a Range

    Hi guys!

    I am using this formula:

    =SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

    What I need to do is to be able to target a cell and use the content of that cell in my "text" section, instead of actually typing the text.

    Exemple:

    =SUM(LEN(A5:A142)-LEN(SUBSTITUTE(A5:A142,"Shot by Guillaume Poulin","")))/LEN("Shot by Guillaume Poulin")


    Let's assume I have:

    A143 ; Guillaume Poulin
    A144 ; Vincent Lecavalier
    A145 ; Patrick Kane


    My goal is that in my formula I can say "Shot by A143" instead of "Shot by Guillaume Poulin", so that I can refer to cells instead of actual text. That would prevent me from having to manually change the words.

    That for the help!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1