counting characters

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

KK,

To count D's use the formula

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

HTH,
Bernie
MS Excel MVP

"KK" wrote in message
...
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

It would be possible to write a macro to do the job, but this would not
be exactly straghtforward. I guess a formula approach would do just as
well. You do not say how you want the counts shown or whether all
letters of the alphabet might be included. Also duplicate letters will
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 single
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.


---
Message posted from http://www.ExcelForum.com/

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"KK" wrote in message ...
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

Thanks for the help, this looks a lot simpler.

Deep respect.

KK


"KK" wrote in message
...
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

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.

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!

--

Regards,

Peo Sjoblom

"TKT-Tang" wrote in message
om...
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.