+ Reply to Thread
Results 1 to 5 of 5

Counting strings when may be 2 strings in a cell

  1. #1
    Registered User
    Join Date
    09-07-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2013
    Posts
    5

    Counting strings when may be 2 strings in a cell

    I want to count the number of times a particular string appears in a worksheet and place the result in another cell. Sometimes the same string appears in a cell 2 times and I want to count this instance twice (I am counting teams in a sporting fixture and sometimes teams from the same clubs play each other eg SaintsDX vs SaintsMT)

    I have tried Countif but this only counts the number of cells containing the string and does not account for cases where the string appears twice in a cell. The only thing I have found that counts the string twice if it appears twice in a cell is Find and Replace. However, I also want to capture the result and cut and paste it into another cell - doing Find and Replace I can only see the number of instances of it and then type into another field.

    Any ideas? Ideally I am looking for a manual way to do this (like Find and Replace) which I can record into a macro.

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Counting strings when may be 2 strings in a cell

    Please attached a workbook with examples and desred result.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    09-07-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Counting strings when may be 2 strings in a cell

    I have attached a workbook with an example and desired result
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-07-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Counting strings when may be 2 strings in a cell

    I tried an attachment but not sure if it worked - let me know if not and will try again.

    In the attachment, in cell D8 there are 2 Rupertwood teams which I need to be counted as 2 finds and in E10 there are 2 Rebel teams.

    Thanks

  5. #5
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Counting strings when may be 2 strings in a cell

    Try this:
    =SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(A8,",",REPT(" ",100)),(ROW(INDIRECT("1:255"))-1)*100+1,100))="rupertwood"))

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting strings when may be 2 strings in a cell

    I don't see an attachment.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    09-07-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Counting strings when may be 2 strings in a cell

    I think attachment worked this time
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-13-2013, 08:32 AM
  2. Replies: 2
    Last Post: 03-07-2013, 02:34 AM
  3. Replies: 9
    Last Post: 12-21-2012, 04:18 AM
  4. Replies: 5
    Last Post: 02-15-2012, 09:57 AM
  5. Replies: 3
    Last Post: 05-28-2011, 01:43 PM

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