+ Reply to Thread
Results 1 to 11 of 11

Identify specific numeric values in a cell such as "1" without counting "11" as two "1"s

  1. #1
    Registered User
    Join Date
    06-02-2016
    Location
    Ventura, California
    MS-Off Ver
    Excel 2013
    Posts
    5

    Identify specific numeric values in a cell such as "1" without counting "11" as two "1"s

    I have a spreadsheet that is looking at individual cells and counting specific values that have been entered in them. For example: Cell G2 has 1,1,3,11,7,1 entered in it. I need another cell to count only the "1"s. However, it views 11 as two 1s as well so my result shows 5 when it should be 3. Here is my formula I'm using.

    =SUM(LEN(G2)-LEN(SUBSTITUTE(G2,"1","")))/LEN("1")

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    how about for this try...
    =SUM(LEN(G2)-LEN(SUBSTITUTE(G2,"1,","")))/LEN("1,")
    added the commas after the 1s
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    this might work better... =LEN(SUBSTITUTE(G2,"11",""))-LEN(SUBSTITUTE(G2,"1",""))
    because the one I proposed in post #2 will count 11, instead of the last 1 since a comma doesn't follow it, so 1,1,1 would only count as 2 instead of 3.

  4. #4
    Registered User
    Join Date
    06-02-2016
    Location
    Ventura, California
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    Tried it and it works if 11 is the only extra item with the number I'm looking for but I still have the same issue of it counting the extra 1s if there are numbers like 13,15,21, etc. The column with multiple numbers are reviews from auditors that are watching for errors. In rare cases, the same error can be made twice so I have to capture that. So I need this to isolate individual numbers. Same issue would happen with any single digit as it shows up again, like 2 will be in 12, all 20s, 32. I appreciate the effort on that try though. thank you so much.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    maybe you could post a sample workbook with a good representative sample of what you have AND the results you want, which numbers to count and which to not count. Otherwise people will just be guessing at formulas. Click on go advanced below this window, then midway down click on manage attachments then browse to the sample file then upload and close the window then submit reply.

  6. #6
    Registered User
    Join Date
    06-02-2016
    Location
    Ventura, California
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    Here is a test copy of the worksheet with items not important to this question stripped down. I'm very new to these forums so please forgive any ignorance on my part. I've included some explanation and details directly on the worksheet.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    484

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    See if this works for you:

    =(LEN(SUBSTITUTE(","&G2&",",",",",,"))-LEN(SUBSTITUTE(SUBSTITUTE(","&G2&",",",",",,"),",1,",",,")))/LEN("1")

  8. #8
    Registered User
    Join Date
    06-02-2016
    Location
    Ventura, California
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    I'm about to leave the office for the day but I plugged this in and off of first glance and a few quick tests, it seems to do the trick. I will try more when I'm back in on Tuesday. However, so far, so good. THANK YOU SO MUCH!

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    Another way. Please try in B2 and fill across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  10. #10
    Registered User
    Join Date
    06-02-2016
    Location
    Ventura, California
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    This actually seems to work the best. I was running into issues when the previous code got into the errors that had double digits but by replacing the A2 reference with the actual cell containing error findings, then replacing B2 with the target cell, it seems to work like a charm. Thank you so much for the assistance.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

    You are welcome. Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  7. Replies: 3
    Last Post: 02-16-2011, 02:55 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