Macro to count a specific substring within a string

[Daddy007]

Hello All,

I'm in need of of a macro that can count certain characters within a string of selected cells.

Thanks for any assistance.

[PCI]

Is the list of characters limited?
Can you prepare a short sample.

[Daddy007]

Unlimited list of characters text or numbers

Example
wdfrt2u7k2y
nxwrv132nxlyeu09
tnb5sx7744477qzn

Then used the macro code to count how may times the number 2 appeared in the list,
or how many times nx appeared in the list
or how may times the number 77 appeared in the list.

This code with a active button on the sheet would help greatly.

Thanks again for your assistance.

[nilem]

maybe UDF

Please Login or Register  to view this content.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Daddy007]

Ok, if i have done anything that has offended you then i'm sorry, but there is no need for this kind of attitude.

Thanks for your help all the same.

[mikerickson]

VBA isn't needed.
If you have your data in A1:A10 and the string you want to count in B1, then the formula
=(SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,""))))/LEN(B1)

will return the number sought.

Note that "777" is counted as having one "77" and "7777" has two.

[Daddy007]

Thanks for the help on this problem.
Just what i needed! and if I'm not stating my problem on the post correctly i apologize, I'm a newbie.

[Daddy007]

Thanks for the help on this problem.
Just what i needed! and if I'm not stating my problem on the post correctly i apologize, I'm a newbie.