How many numbers

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
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
 

Some videos you may like

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
Joined
Apr 29, 2016
Messages
312
Try this in D1:

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

Enter with Ctrl+Shift+Enter.
 
Solution

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,366
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top