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

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

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

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

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

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
==========================

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