How many numbers

Ezguy4u

Active Member
Joined
Feb 10, 2010
Messages
336
Office Version
  1. 365
Platform
  1. Windows
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.
Thanks in advance

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this in D1:

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

Enter with Ctrl+Shift+Enter.
 
Upvote 0
Solution
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&" "))
 
Upvote 0
Try this at D1 and copy down,
=SUM(--(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("-",TRUE,$A$1:$A$11),"-","</b><b>")&"</b></a>","//b")=$C1))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top