Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lee lee is offline
external usenet poster
 
Posts: 184
Default Count letter string, e.g. h/EL/p

I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most frequent
English words). e.g. I would like to count how many words contain the string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E" (mAdE)
occurs.

Thanks

Lee
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 10:12:07 -0700, Lee wrote:

I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most frequent
English words). e.g. I would like to count how many words contain the string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E" (mAdE)
occurs.

Thanks

Lee


Just use the COUNTIF worksheet function with wild cards if you want a case
insensitive count.

e.g:

=COUNTIF(A:A,"*ee*")
=COUNTIF(A:A,"*A?E*)

--ron
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default Count letter string, e.g. h/EL/p

try:

=SUM(N(ISNUMBER(SEARCH("a?e",A2:A1000))))

=SUM(N(ISNUMBER(SEARCH("ee",A2:A1000))))

Enter with Ctrl+Shift+Enter

"Lee" wrote:

I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most frequent
English words). e.g. I would like to count how many words contain the string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E" (mAdE)
occurs.

Thanks

Lee

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default Count letter string, e.g. h/EL/p

One way:

With the string that you are looking for in D1 try:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),D1,"")))/LEN(D1)

Array entered with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lee" wrote in message
...
I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default Count letter string, e.g. h/EL/p

..... why do I make life difficult!!

"Ron Rosenfeld" wrote:

On Mon, 3 Sep 2007 10:12:07 -0700, Lee wrote:

I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most frequent
English words). e.g. I would like to count how many words contain the string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E" (mAdE)
occurs.

Thanks

Lee


Just use the COUNTIF worksheet function with wild cards if you want a case
insensitive count.

e.g:

=COUNTIF(A:A,"*ee*")
=COUNTIF(A:A,"*A?E*)

--ron



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Count letter string, e.g. h/EL/p

Try this:

For "ee" :

=COUNTIF(A1:A10,"*ee*")

For "A, any letter, E" (mAdE) :

=COUNTIF(A1:A10,"*a?e*")

--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 136
Default Count letter string, e.g. h/EL/p

Or
=SUM(1*(LEN(A1:A3)<LEN(SUBSTITUTE(A1:A3,"ee","")) ))

"Sandy Mann" wrote:

One way:

With the string that you are looking for in D1 try:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),D1,"")))/LEN(D1)

Array entered with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lee" wrote in message
...
I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 136
Default Count letter string, e.g. h/EL/p

Or
=SUM(1*(LEN(A1:A1000)<LEN(SUBSTITUTE(A1:A1000,"ee ",""))))

"Sandy Mann" wrote:

One way:

With the string that you are looking for in D1 try:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),D1,"")))/LEN(D1)

Array entered with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lee" wrote in message
...
I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 18:26:14 +0100, "Sandy Mann"
wrote:

One way:

With the string that you are looking for in D1 try:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),D1,"")))/LEN(D1)

Array entered with Ctrl + Shift + Enter



How does that work with wildcards? e.g. a?e for mAdE as the OP requested?
--ron
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 10:30:00 -0700, Toppers
wrote:

.... why do I make life difficult!!


At least it works!
--ron


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default Count letter string, e.g. h/EL/p

"Ron Rosenfeld" wrote in message
...
How does that work with wildcards? e.g. a?e for mAdE as the OP requested?


mmmm....

Didn't think it through did I?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 19:06:17 +0100, "Sandy Mann"
wrote:

The < operator is better but Ron has us both beaten hands down if the OP
only want to count words not multiple occurrences within words.
--
Regards,


For ease of formulating, I'd probably use a regular expression UDF if he wanted
multiple occurrences within words


--ron
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lee lee is offline
external usenet poster
 
Posts: 184
Default Count letter string, e.g. h/EL/p

Thanks,

How would I count OU not OUGH (count OUT not THOUGH)?

"T. Valko" wrote:

Try this:

For "ee" :

=COUNTIF(A1:A10,"*ee*")

For "A, any letter, E" (mAdE) :

=COUNTIF(A1:A10,"*a?e*")

--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
I want to count how many times a string of letters occurs in each row in a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee




  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 19:46:01 -0700, Lee wrote:

Thanks,

How would I count OU not OUGH (count OUT not THOUGH)?


How much variability might there be in your patterns?

Will it always be either an unqualified series of letters or a series of
letters that is NOT followed by another series?

Or are there other possibilities also?
--ron


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lee lee is offline
external usenet poster
 
Posts: 184
Default Count letter string, e.g. h/EL/p

I am counting how often specific digraphs (e.g. er, ou, ought, ow) occur in
the most common 1000 words. Sometimes the digraph will be embedded within a
word and sometimes it occurs at the end.

"Ron Rosenfeld" wrote:

On Mon, 3 Sep 2007 19:46:01 -0700, Lee wrote:

Thanks,

How would I count OU not OUGH (count OUT not THOUGH)?


How much variability might there be in your patterns?

Will it always be either an unqualified series of letters or a series of
letters that is NOT followed by another series?

Or are there other possibilities also?
--ron

  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Count letter string, e.g. h/EL/p

On Mon, 3 Sep 2007 22:36:00 -0700, Lee wrote:

I am counting how often specific digraphs (e.g. er, ou, ought, ow) occur in
the most common 1000 words. Sometimes the digraph will be embedded within a
word and sometimes it occurs at the end.


If my understanding of digraphs is correct, then the only variation you will
need is to separate out digraphs from trigraphs and tetragraphs, so a routine
which allows you to specify a character sequence that may not be followed by
other character(s) should be sufficient.

I also presume that if a word should have two identical digraphs (and I don't
know if any exist), that you would only want to count it once.

That being the case, this UDF should suffice:

To use the UDF, enter a formula of the type:


=SUMPRODUCT(ReCount(range,string_to_find,[string_not_to_follow],[search_entire_string]))

The arguments within brackets [...] are optional.

The [search_entire_string] option will, is set to TRUE, will count multiple
matches within a single string. In other words, looking for "ee" in
beekeeping, the function will return two; otherwise it will return one.

To return all the "ou", you would use:

=SUMPRODUCT(recount(A1:A1000,"ou"))

To return only "ou" not followed by "gh", you would use:

=SUMPRODUCT(recount(A1:A1000,"ou","gh"))

and to count all of the "ou" not followed by "gh" in each word as a separate
entry, you would use:

=SUMPRODUCT(recount(A1:A1000,"ou","gh",TRUE))

As written, the UDF is case insensitive. This could be made an optional
argument, if required.

To enter the VBA code, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and enter the code below into the window that opens:

================================================== ==========
Option Explicit

Function ReCount(s, p, Optional NF = "", Optional g As Boolean = False)
's: the string being searched. It can be a string, _
a cell reference, or a range of cells
'p: the pattern to search for
'NF: an optional pattern that should NOT FOLLOW p
'g: if TRUE, will cause multiple matches within a single string

If NF < "" Then
p = p & "(?!" & NF & ")"
End If
Select Case VarType(s)
Case Is = vbString
ReCount = Cnt(s, p, g)
Case Is vbArray
Dim c As Range, i As Long
Dim t
ReDim t(0 To s.Count)
For Each c In s
t(i) = Cnt(c.Text, p, g)
i = i + 1
Next c
ReCount = t
End Select
End Function

Function Cnt(str, sPattern, g)
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = g
re.ignorecase = True

Set mc = re.Execute(str)
Cnt = mc.Count
End Function
================================
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to count the match letter within a string? Eric Excel Discussion (Misc queries) 7 August 22nd 07 03:43 AM
letter number count Alicia Excel Worksheet Functions 11 May 25th 07 12:21 AM
I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW spring022377 Excel Worksheet Functions 13 February 16th 07 09:39 AM
How can I count the number of times a letter repeats in a string? Wiley Excel Worksheet Functions 3 May 11th 06 06:53 PM
count cells in a coloumn with letter x in it sarg Excel Worksheet Functions 2 September 24th 05 09:01 PM


All times are GMT +1. The time now is 08:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"