# of each letter in a list of words

Jim Brass

unread,

Dec 25, 2001, 9:18:37 AM12/25/01

to

Does anyone know how to set up a macro or function that will count how many
times each letter of the alphabet is used in a word list? For example, in a
list of the 50 states, how many s's are there. In fact, can a table list how
many of each letter is found? Also can you also determine which letters are
used only once or not at all? Thanks for any help. Jim

Chip Pearson

unread,

Dec 25, 2001, 9:35:24 AM12/25/01

to

Jim,

Try something like

Function CountLetters(InRange As Range, S As String) As Long
Dim Rng As Range
For Each Rng In InRange
CountLetters = CountLetters + Len(Rng.Text) - _
Len(Replace(Rng.Text, S, "", , , vbTextCompare))
Next Rng
End Function

Then call this with from a cell with =CountLetters(A1:A5,"s") or in VBA with
code like

MsgBox "There are " & CountLetters(Range("A1:A5"), "s") & " letters found."

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Jim Brass" <jbr...@cfu.net> wrote in message news:a0a1t5$mf2$1...@news.cfu.net...

Peo Sjoblom

unread,

Dec 25, 2001, 12:58:17 PM12/25/01

to

Jim,

one possible way, assume that the states are in A1:A50, in B1 put

=CHAR(97+ROW(1:1)-1)

drag the fillhandle to copy it down to B26

in C1 put

=SUM(LEN($A$1:$A$50)-(LEN(SUBSTITUTE(LOWER($A$1:$A$50),B1,""))))

enter it with Ctrl+Shift&Enter and double click the lower right corner of C1

Now you should have a list starting with number of "a(s)" regardless of
upper or lower case going down to "z"..

-
Regards,

Peo Sjoblom

"Jim Brass" <jbr...@cfu.net> wrote in message
news:a0a1t5$mf2$1...@news.cfu.net...

Gord Dibben

unread,

Dec 25, 2001, 1:12:10 PM12/25/01

to

Merry Xmas Chip.

This Function hangs on me at "Replace". "Sub or Function not defined". I can
use the REPLACE function on its own in a cell.

Do I need to add a Reference to something? Maybe another eggnog would help??

Thanks, Gord Dibben Excel MVP - XL97 SR2

Chip Pearson

unread,

Dec 25, 2001, 1:22:01 PM12/25/01

to

Gord,

Replace was added in VBA6 (Office2000). You can use the Excel Substitute
function instead.

For Each Rng In InRange
CountLetters = CountLetters + Len(Rng.Text) - _

Len(Application.WorksheetFunction.Substitute(UCase(Rng.Text), UCase(S),
""))
Next Rng

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Gord Dibben" <gdi...@shaw.ca> wrote in message
news:c4gh2u8a2qvj0757g...@4ax.com...

Gord Dibben

unread,

Dec 25, 2001, 1:35:47 PM12/25/01

to

Thank you Chip

Does the trick. Just gonna have to upgrade one of these days.

Gord

Jim Brass

unread,

Dec 25, 2001, 3:56:57 PM12/25/01

to

Thanks Peo Sjoblom. It works! One quick question though: What do I change if
all the words are in capital letters. Do I change to =CHAR(113+ROW(1:1)-1)?
And does LOWER change to UPPER. Thanks again!! Jim

"Peo Sjoblom" <ter...@yahoo.com> wrote in message
news:e3iNb3WjBHA.4596@tkmsftngp04...

Peo Sjoblom

unread,

Dec 25, 2001, 4:08:53 PM12/25/01

to

Jim,

=CHAR(65+ROW(1:1)-1)

will give you uppercase letters

and yes you can change lower to upper in the formula if the letters in the
list are caps, or you can bypass that by using

=SUM(LEN($A$1:$A$50)-(LEN(SUBSTITUTE(UPPER($A$1:$A$50),UPPER(B1),""))))

(or use lower in both instances) will work regardless of upper/lower case
in the letter list

--
Regards,

Peo Sjoblom

"Jim Brass" <jbr...@cfu.net> wrote in message

news:a0ap80$o59$1...@news.cfu.net...

Jim Brass

unread,

Dec 25, 2001, 5:05:19 PM12/25/01

to

Thanks a Lot!!!

"Peo Sjoblom" <ter...@yahoo.com> wrote in message

news:eokZ6hYjBHA.4792@tkmsftngp04...