count 2 or more words in a cell

I am trying search & count 2 or more words within a cell using a worksheet function. See below example:

The texts are in A1 (only)
Entered by John at 02:19 PM on 02/13/2007
Entered by Smith at 02:20 PM on 02/13/2007
Entered by John at 02:30 PM on 02/13/2007
Entered by John at 02:21 PM on 02/12/2007
Entered by John at 02:22 PM on 02/11/2007

The words I am looking for are "John" and "02/13/2007" and the count should be 2. I bolded what should be counted.Can you help me?

Your help on this is appreciated.

Statsman

Hi

Here's one rather crude way.

C1: =SEARCH("john*02/13/2007",A1)
C2: =SEARCH("john*02/13/2007",$A$1,C1+1)
Copy C2 down to C4
D1: =COUNT(C1:C4)

HTH

rylo

Thank you very much! You have been very helpful!

Statsman

I tested the function and it works great until I added another cell with the same search criteria but different data.

The texts are in A1 & A2 (The words can be in a cell or range of cells).
Cell A1
Entered by John at 02:19 PM on 02/13/2007
Entered by Smith at 02:20 PM on 02/13/2007
Entered by John at 02:30 PM on 02/13/2007
Entered by John at 02:21 PM on 02/12/2007
Entered by John at 02:22 PM on 02/11/2007

Cell A2
Entered by John at 03:19 PM on 02/13/2007
Entered by John at 04:19 PM on 02/13/2007
Entered by Smith at 02:20 PM on 02/12/2007

The count should be 4.

Thanks again for your help!
Statsman

Statsman

I've modified the function so that it doesn't need the iserror test.

Please Login or Register  to view this content.

However, I can't think of any way to do this without using a UDF. Or rather, the original way could be expanded so that there was a block for each cell, then add all the outputs. However, I don't see that as a viable alternative.

From what I can see, you have a highly specific request. You may be able to speed the spreadsheet up by having a macro do the calculation on request, but that brings a completely different set of problems.

Maybe someone else will come up with something if this isn't acceptable.

rylo

Hi Rylo!

I greatly appreciate for you helping me on this. Your code is acceptable and it actually works great.

However, the more tests I make, there is a problem I encounter. The sample below should count only 1 but when I used the function, it counts 2. I bolded where is the additional count happening.

Cell A1
Entered by John at 02:19 PM on 02/13/2007
Entered by John at 02:20 PM on 02/12/2007
Entered by Smith at 02:30 PM on 02/13/2007
Entered by Smith at 02:21 PM on 02/12/2007
Entered by Andrea at 02:22 PM on 02/11/2007

Is there a workaround on this?

Thanks again Rylo for your help!

Statsman

You beat me too it. I thought of this problem last night and been mulling on a solution.

Please Login or Register  to view this content.

Input as

Please Login or Register  to view this content.

Let me know how it goes.

rylo

it workkkkkkkssssss! You are amazing!I am really impressed despite of this complicated issues (for me anyway) you always have the workaround. Thanks again

After testing the function with so may data, I noticed that it is taking a lot of time to execute and get the result. It often slows down the system and sometimes crashes.

Here is an idea that i believe will resolve my dilemna:
1. Remove all spaces
2. Delete unwanted chars such as □
3. Remove unwanted texts, in this case the time (I underlined and bolded these texts after word "at" and before "by")

A1 (Before)
Entered on 02/13/2008 at 02:00 PM by John:
Entered on 02/13/2008 at 02:00 PM by Smith:
Entered on 02/11/2008 at 02:00 PM by John:
Entered on 02/1(/2008 at 02:00 PM by John:

A1 (After)
Enteredon02/13/2008atbyJohn:Enteredon02/13/2008atbySmith:Enteredon02/11/2008atbyJohn:Enteredon02/11/2008atbyJohn:

I was able to create a formula for 1 & 2, but I got stuck in #3. Is there a function that removes the unwanted texts after and before a specific word?

Pretending the criteria is located in K3 which is "Enteredon02/13/2008atbyJohn:", I can count the number of times K3 value occured in cell A1 using the function =SUM(LEN($a$1)-LEN(SUBSTITUTE($a$1,K3,"")))/LEN(K3).

You help again is appreciated.

Statsman

Hi

OK, try this

1) A function

Please Login or Register  to view this content.

2) Data in A1
3) E1: =myfunc(A1)
4) J1: 02/13/2008John
5) H1: =SUM(LEN($E$1)-LEN(SUBSTITUTE($E$1,J1,"")))/LEN(J1)

See how that goes.

rylo

This is excellent! the function's execution is faster. You are the greatest!