+ Reply to Thread
Results 1 to 9 of 9

Count cells where characters 2 to 8 is a number

  1. 08-16-2015, 08:16 PM #1
    DD1
    DD1 is offline
    Registered User
    Join Date
    07-12-2005
    Location
    Canberra, Australia
    MS-Off Ver
    2007
    Posts
    43

    Count cells where characters 2 to 8 is a number

    I need to identify surgical codes from a list. The criteria for this is:

    1. Surgical codes are those that begin with a 7 digit number – excluding those that begin with “ 955”.
    2. However, the first character in all the codes is always a space, so it is really characters 2 to 8

    The attached spreadsheet identifies the codes that I want to count as surgical.

    I think this can be done with either a ‘countifs’ function or a ‘count(if’ array function, but I have no idea how to write it.

    Can anyone help please?

    Many thanks
    David
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-16-2015, 08:32 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count cells where characters 2 to 8 is a number

    Try this formula in B1 and copy down.

    =IF(LEFT(A1,4)=" 955","",IF(FIND(":",A1)=9,1,""))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 08-16-2015, 08:34 PM #3
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Count cells where characters 2 to 8 is a number

    formule in column C


    Kind regards
    Leo
    Last edited by LeoTaxi; 08-16-2015 at 08:48 PM.
    Reply With QuoteRegister To Reply
  4. 08-16-2015, 08:38 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Count cells where characters 2 to 8 is a number

    Try this, copied down...
    =IF(OR(LEFT(TRIM(A1),3)="955",ISERROR(--LEFT(A1,8))),"",1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 08-16-2015, 08:38 PM #5
    DD1
    DD1 is offline
    Registered User
    Join Date
    07-12-2005
    Location
    Canberra, Australia
    MS-Off Ver
    2007
    Posts
    43

    Re: Count cells where characters 2 to 8 is a number

    Thanks for the replies. I should have said that my data has the codes in multiple columns, not just one column.
    So in my example, I would particularly like to enter the formula in a single cell (B12 in my spreadsheet) and get the result, rather than entering a formula against each row (and column) and then summing the results of those formulas.
    Is that possible?
    Thanks
    David
    Reply With QuoteRegister To Reply
  6. 08-16-2015, 08:38 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Count cells where characters 2 to 8 is a number

    @ LeoTaxi, welcome to the forum

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Reply With QuoteRegister To Reply
  7. 08-16-2015, 09:03 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count cells where characters 2 to 8 is a number

    try this to get the total

    =SUMPRODUCT(--(ISNUMBER(--LEFT(A1:A11,8))))-COUNTIF($A$1:$A$11," *955*")
    Reply With QuoteRegister To Reply
  8. 08-16-2015, 09:07 PM #8
    DD1
    DD1 is offline
    Registered User
    Join Date
    07-12-2005
    Location
    Canberra, Australia
    MS-Off Ver
    2007
    Posts
    43

    Re: Count cells where characters 2 to 8 is a number

    That's perfect.
    Thanks very much. Really appreciate it
    Regards,
    David
    Reply With QuoteRegister To Reply
  9. 08-16-2015, 09:17 PM #9
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Count cells where characters 2 to 8 is a number

    My apologies,

    i removed it because there was a better solution


    Kind regards
    Leo
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count Number of Characters in a Selection
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2014, 03:09 PM
  2. [SOLVED] how to count number of characters in a cell
    By Mile029 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 11:09 PM
  3. How to count the number of characters while typing
    By Tam123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 10:50 AM
  4. Count the number of Characters in a cell?
    By IGinc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2009, 03:02 AM
  5. Count the number of different characters in a range
    By aderougemont in forum Excel General
    Replies: 4
    Last Post: 01-12-2009, 11:36 AM
  6. How to count characters in a number of cells?
    By Staalander in forum Excel General
    Replies: 8
    Last Post: 01-11-2007, 11:36 AM
  7. [SOLVED] Count number of characters in merged cells + adjusting rowheight
    By Luc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-28-2006, 07:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1