Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count the match letter within a string? | Excel Discussion (Misc queries) | |||
letter number count | Excel Worksheet Functions | |||
I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW | Excel Worksheet Functions | |||
How can I count the number of times a letter repeats in a string? | Excel Worksheet Functions | |||
count cells in a coloumn with letter x in it | Excel Worksheet Functions |