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