# How many numbers

#### Ezguy4u

##### Board Regular
Ok so I want to count Ones, Twos and so on in column A. In Column C is just 1 thru 11. Column D is the output. Now I filled in column D by just counting how many ones and twos and so on. I hope I have the right amount in column D. Column A will have the dashes and could have no less than 3 numbers but no more than 5 numbers. I can go with a formula or VBA.

Rank 1.xlsx
ABCD
16-1-913
211-2-423
310-9-11-1-23
410-9-1141
52-11-9-15
661
77
88
994
10102
11114
Rank

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Phuoc

##### Active Member
Try this in D1:

=COUNT(SEARCH("-"&C1&"-","-"&\$A\$1:\$A\$5&"-"))

Enter with Ctrl+Shift+Enter.

#### Dossfm0q

##### Banned User
Excel Formula:
``=(LEN(UPPER(" "&TRIM(SUBSTITUTE(TEXTJOIN(" ",TRUE,\$A\$1:\$A\$11),"-"," "))&" ")) - LEN(SUBSTITUTE(UPPER(" "&TRIM(SUBSTITUTE(TEXTJOIN(" ",TRUE,\$A\$1:\$A\$11),"-"," "))&" "),UPPER(" "&\$C1&" "),""))) / LEN(UPPER(" "&\$C1&" "))``
count wrd.xlsx
ABCD
1 6 - 1 - 913
2 11 - 2 - 423
3 10 - 9 - 11 -1 - 230
4 10 - 9 - 1141
5 2 - 11 - 9 -150
661
770
880
994
10102
11114
12120
13130
Sheet2
Cell Formulas
RangeFormula
D1:D13D1=(LEN(UPPER(" "&TRIM(SUBSTITUTE(TEXTJOIN(" ",TRUE,\$A\$1:\$A\$11),"-"," "))&" ")) - LEN(SUBSTITUTE(UPPER(" "&TRIM(SUBSTITUTE(TEXTJOIN(" ",TRUE,\$A\$1:\$A\$11),"-"," "))&" "),UPPER(" "&\$C1&" "),""))) / LEN(UPPER(" "&\$C1&" "))

#### KP117

##### New Member
Try this at D1 and copy down,
=SUM(--(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("-",TRUE,\$A\$1:\$A\$11),"-","</b><b>")&"</b></a>","//b")=\$C1))

#### Ezguy4u

##### Board Regular
Thanks to everyone for your time and formulas. Now I don't have excel 365 so I can't use that Filterxml KP117. Dossfm0q I will be looking into your formula to try and understand it a little better. It works but it is long. Phuoc I like yours just because it short and sweet. Again thanks to all.

#### Fluff

##### MrExcel MVP, Moderator
Now I don't have excel 365 so I can't use that Filterxml
The Filterxml function has been around since 2013, so you can use it.

Replies
9
Views
176
Replies
3
Views
60
Replies
3
Views
55
Replies
3
Views
356
Replies
1
Views
350

1,127,245
Messages
5,623,586
Members
415,981
Latest member
Baltwin

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