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

1. ## 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. ## Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

=SUM(LEN(G2)-LEN(SUBSTITUTE(G2,"1,","")))/LEN("1,")
added the commas after the 1s

3. ## 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. ## 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. ## 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. ## 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.

7. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`

10. ## 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. ## Re: Identify specific numeric values in a cell such as "1" without counting "11" as two "1

You are welcome. Glad to help.

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

#### 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