+ Reply to Thread
Results 1 to 6 of 6

Counting Numbers within a Single Cell

  1. #1
    Registered User
    Join Date
    04-02-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Counting Numbers within a Single Cell

    I am working with a really messy excel output from a data warehouse. In cleaning it up into something workable I am wondering if there is a way to count the number of occurrences of a few specific numbers within a single cell.

    See the attached spreadsheet to see what I am working with and what I am trying to accomplish.

    In cell A2 the numbers 12, 13, 14, 15, and 16 occur repeatedly, separated by semi colons and in varying amounts. I would like to find a formula or formulas that will count the number of 12s, the number of 13s, the number of 14s, the number of 15s and the number of 16s that occur within the cell.

    Any suggestions?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Counting Numbers within a Single Cell

    C2: =(LEN($A2)-LEN(SUBSTITUTE($A2,MID(C$1,5,2),"")))/2

    and drag across.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-02-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Counting Numbers within a Single Cell

    Why is the /2 needed at the end?

    This is awesome!

    Thank you!!!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Counting Numbers within a Single Cell

    Because you are replacing two digit numbers: 11, 12, 13, 14, ...

    The formula replaces all the numbers by nothing, thus reducing the length of the substituted value by the count of the substituted value times the length of the substituted value. To get the count, you need to divide by 2. if you were looking for 100+, you'd divide by 3, a 1000+, by 4, etc.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    04-02-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Counting Numbers within a Single Cell

    Got it.

    Thanks again!!!!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Counting Numbers within a Single Cell

    You're welcome.

+ Reply to 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