Results 1 to 7 of 7

Thread: Count the occurrences of text, characters, or words in Excel

  1. #1
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14

    Count the occurrences of text, characters, or words in Excel

    Hi All,

    I need a formula to find the occourance of a text in a cell.

    for Example: In cell there is a text "Rarrurrlas" i want to find the number occourance of "rr" in the given text.

    and ans should be : 2

    I am using below formula: =LEN("Rarrurrlas")-LEN(SUBSTITUTE("Rarrurrlas","rr",""))

    but it is giving me ans : 4

    Thanks
    Rahul Kumar Singh

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Try

    =(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(C1),"")))/LEN(C1)

    where C1 houses rr
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Thanks for Quick Response,

    However I don't want to put my search string in another cell.
    Is there any other formula, which can give me the appropriate result?

    Thanks in Advance.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Replace the C1 with the string "rr"
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    May 2011
    Posts
    10
    Rep Power
    0
    littleiitin,

    Try:

    In A1:
    Rarrurrlas

    In B1:
    =(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2
    The result in B1 would be:
    2


    The last number in the formula, the 2, is the actual length of the string rr that you are searching for.
    =(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2



    If cell A1 contained:
    RarruXXlas

    And cell B1 containd the same formula:
    =(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2

    The result in B1 would be:
    1



    Have a great day,
    Stan
    Last edited by stanleydgromjr; 09-24-2011 at 06:12 PM.

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi All,

    SUBSTITUTE is case sensitive. So =SUBSTITUTE("RaRRurrlas","rr","") would become "RaRRulas" (without quotes)

    The following would give you the count within a range

    =SUMPRODUCT((LEN(A2:A10)-LEN(SUBSTITUTE(UPPER(A2:A10),UPPER(C2),"")))/LEN(C2))

    where C2 houses the search string


    @ Stan, Thanks for explaining the formula in detail.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Thanks to all for your valuable solutions.

    Special thanks to Admin for identifying that if cell contains number only then formula given by us will not work.
    Also thanks for proper solution.

Similar Threads

  1. Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 15
    Last Post: 12-20-2016, 09:47 AM
  2. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  3. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  4. Replies: 10
    Last Post: 12-10-2012, 11:28 PM
  5. Count words having more than one character in a cell
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 03-13-2012, 08:29 PM

Tags for this Thread

Posting Permissions

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