# count certain numbers in a string of 12 numbers

#### Marshall1970

##### New Member
I have a field that contains the following: 012100002030

I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.

Examples:

001000002011
111111000022
401110000000

the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2.
the second loan would meet the criteria as it contains two 2's.
the 3rd loan would meet the criteria as it contains a number higher than 2.

thank you, hopefully I have provided enough info.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Oaktree

##### MrExcel MVP
Try:

Code:
``=OR(SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0=2))>=2,SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0>2)))``

#### Marshall1970

##### New Member
that works perfect. Thanks!

#### Marshall1970

##### New Member
one more thing, if I want the value to return "Yes" instead of TRUE or "No" instead of FALSE, how would I do that?

##### MrExcel MVP
=IF( *insert formula *<INSERT formula>,"YES","No")

#### Marshall1970

##### New Member
ok, thanks all that works great. One more twist..

if the first digit is a 1, then I want to say "Exclude" regardless of any other digits
if the last digit is a 2 and all other digits are 0, then I want it to say "Include"

Examples:

000000000002 - include
111111111111 - include
200000300000 - exclude
100000000000 - exclude
000003000000 - exclude
000020000020 - exclude

#### Oaktree

##### MrExcel MVP
Wrap two more IF statements around your formula:

=IF(LEFT(A1,1)+0=1,"Exclude",IF(RIGHT(A1,1)+0=2,"Include",IF( *insert formula *,"YES","No")))

worked, thanks

Last edited:

#### Marshall1970

##### New Member
so close, if I include only the first if statement, it works. when i put in the 2nd if I get an errror msg..any ideas?

=IF(LEFT(AT2,1)+0=1,"IneligiblePmtHist",if(right(AT2,1)+0=2,"IneligiblePmtHist",IF(OR(SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0=2))>=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0>2))),"IneligiblePmtHist",""))

#### Oaktree

##### MrExcel MVP
My bad... it's exceeding the limit of 7 nested functions.

But, because they all return the same thing, you can combine them:

=IF(OR(LEFT(AT2,1)+0=1,RIGHT(AT2,1)+0=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0=2))>=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0>2))),"IneligiblePmtHist","")

Replies
1
Views
156
Replies
0
Views
107
Replies
3
Views
402
Replies
18
Views
301
Replies
11
Views
257

1,191,074
Messages
5,984,480
Members
439,891
Latest member
maikii

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back