i need to count how many times a word is in string in the same cell

so is their way to do that either by funtions or macros

Hi,

Am Sat, 20 Oct 2012 07:11:50 +0000 schrieb tamer:

i need to count how many times a word is in string in the same cell

your string in A1, the word you search in B1:
=SUM((LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER(B1),"")))/LEN(B1))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sat, 20 Oct 2012 07:11:50 +0000, tamer wrote:


i need to count how many times a word is in string in the same cell

so is their way to do that either by funtions or macros

Hans solution will count the instances of a substring within a string. So given:

A1: Is this a good time for a timely discussion about timepieces.
B1: time

Although the substring "time" occurs three times, I would consider that the word "time" only occurs once.

If that is what you mean, then I would recommend a User Defined Function (UDF)

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=WordCount(A1,B1)

where A1 is the StringToSearch and B1 contains the WordToCount

in some cell.

==================================
Option Explicit
Function WordCount(StringToSearch As String, WordToCount As String) As Long
Dim re As Object, mc As Object
Dim sPat As String
sPat = "\b" & WordToCount & "\b"
Set re = CreateObject("vbscript.regexp")
With re
.Pattern = sPat
.ignorecase = True
.Global = True
End With

Set mc = re.Execute(StringToSearch)
WordCount = mc.Count

End Function
==========================

Have a look at the attached. Does this do what you need?

I've used this specific example as it points out a minor flaw in the process. But without more information on what you're actually searching for, it's hard to tell if it will be an issue for you.

thx you guy it works

It do not work i do not know why but thx you i got funtion that can do that

Happy to help :)

dear i want help in one more thing

I have three cells first cell contains the following "forwardtopostapproval re-checking forwardtopostapproval"

the second cell contains staff names " georgesidhom,tamerxxx, kimomicho"

the third cell contins time & date " 12:55 april 2AM ,13:12 may ,15:20 august"

this cells come vertical positions beside each other

how can i analysise this data for example know that the georgesidhom do the action forwardtopostapproval @ 12:55 april 2AM

I'm not sure I fully understand what you mean. Could you post an example workbook?

here it is

the aim is to match the action with the date with the name

On Sat, 20 Oct 2012 16:00:34 +0000, tamer wrote:

It do not work i do not know why but thx you i got funtion that can do
that

As long as you have something that works the way you want. But you should be aware that the other solutions will count that

"the" occurs twice in the phrase: "Is there is a long time since we met them"

I agree with Ron. That's the flaw that I mentioned in my post above.
But as I said, without knowing exactly what the OP is trying to do, it's difficult to judge whether or not that will actually post a problem.

As per your additional post on the same topic:
http://www.excelbanter.com/showthread.php?t=447447

Your example file does not make it clear what you're actually trying to accomplish with this.

Perhaps if you could add a manually generated example of what you want it to look like after the macro/formula has been executed it would help someone understand your need more.

What i want is to know how many action done by the names and what is this action and the dates of each action by pivote for example

Then you will need to provide more than one line of example data.
And as per my previous post, a brief example of what you want it to look like AFTER is always helpful.

I want to find an equation or way to do the followwing in Excel

here is the example

I have a string in one cell and i want for example to count the repetation of word red starting from the right hand side and till the fourth coma (,) only not through over the string

the aim of this is not to count the word i am searching about in the full string i wan to count it in only part of it

here is the example

Blue , red , green , green , red , red , yellow ,

can you help me in that