counting characters

[KK]

Hello,

I have a spreadsheet with cells containing strings of characters. eg DWDE
or WWN or ENWEE etc. Strings are not necessarily the same length but are 5
characters maximum.

I want to count the numbers of each character in each string. so for the 1st
example above I want to end up with the answer 2 for the number of Ds, 1 for
Ws, 1 for Es. For the last example I need 0 for Ds, 3 for Es, 1 for Ns, 1
for Ws etc.

I can do it using the MID function, testing the string character by
character to see if it is equal to 'D' or 'W' etc, and counting logical
'yes' values. It works OK, but it is very messy. Am I missing something more
elegant?

Thanks

KK

 

[Bernie Deitrick]

KK,

To count D's use the formula

=LEN(A1)-LEN(SUBSTITUTE(A1,"D",""))

HTH,
Bernie
MS Excel MVP

 

[BrianB]

It would be possible to write a macro to do the job, but this would no
be exactly straghtforward. I guess a formula approach would do just a
well. You do not say how you want the counts shown or whether al
letters of the alphabet might be included. Also duplicate letters wil
need to be dealt with. One quick method :-

Say your list is in column A.
I would use the next 5 columns to split the string into singl
characters using =MID(A1,1,1) ,=MID(A1,2,1) etc.
I would then use the next 5 columns to do the count
eg. cell G1 would have the formula =COUNTIF($B1:E1,B1)

The row of formulas can then be copied down

 

[David McRitchie]

Missed that question was already answered but note
that SUBSTITUTE is case sensitive.

A1: DWDE
E1: =LEN(A1)-LEN(SUBSTITUTE(A1,"D",""))
F1: =LEN($A1)-LEN(SUBSTITUTE($A1,"E",""))

Substitute is case sensitive, you could use
E1: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"D",""))
to make it case insensitive.

More of similar things on my strings.htm web page.

 

[KK]

Thanks for the help, this looks a lot simpler.

Deep respect.

KK

 

[TKT-Tang]

1. Enter "ENWEE" inti cell A1.
2. Enter the following formula into cell A2 :-
=LEN(A1)-LEN(SUBSTITUTE(A1,"E",""))
3. The formula returns the number of E's.

Regards.

 

[Peo Sjoblom]

You (and anyone else) would probably benefit if you got a newsreader instead
of using
Google for posting. This post was already answered by several people over 36
hours ago!