Check a column for certain characters

if you use a couple of helper colums see attached number or char to count goes in b1 result in e1

Extracting count of alphas is relatively straight forward...

If we assume your first data set appears in A1:A7 then:

=SUMPRODUCT(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"U","")))

which returns 7 (not 8)

Regards numerics, using your 2nd dataset, let's assume that resides in B1:B5

if we wished to count both 1 and 12 such that we get 0 and 3 respectively then we'll be better off reverting to a SUM / COUNTIF type approach given we know the number appears only once and all numerics within the string form part of that single number, eg:

=SUM(COUNTIF(B1:B5,{"1W*","1U*","1B*","1R*","1G*"}))
would return 0

=SUM(COUNTIF(B2:B6,{"12W*","12U*","12B*","12R*","12G*"}))
would return 3

does that help ?

wouldnt
=SUM(COUNTIF(B2:B6,{"12W*","12U*","12B*","12R*","12G*"}))
be a tad hard to manage if you now wanted to count 20's?

Not entirely sure I follow Martin but I'd openly concede no inline array approach is flexible

I guess if you wanted to have the numeric search criteria as cell variables then given the 1-20 assumption perhaps:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

where C1 contains the number of interest be it 1, 6, 12 -- returning 0, 1, 3 respectively (based on B1:B5 dataset)

i just thought th op meant that he would like to count say 2's eg in
20ugbw
2ugbw
20rbgw
answer =1
then count say 20's =2

Yes, that's the assumption made behind all of the suggestions made thus far I think, no ?

my bad didnt read your whole post! just trying to avoid sumproduct lol

Just for clarity - attached is my "thinking" however muddled...

The SUM(COUNTIF approach will work for the numerics just as easily (if not easier) as the SUMPRODUCT if we can assume that as inferred the # is always to be followed by one of a handful of alphas.

i like that countif,didnt know it could be done like that!

I made ok for numbers (with helper column).. But can't get character if it's on beggining or end of the word :-S

Like U here is 7, and it should be 9

zbor, as outlined already, given you need to treat each alpha character separately a SUMPRODUCT approach is more logical.

The COUNTIF approach is viable for the numerics given these exist only once per string (if at all).