Count the duplicate string in the cell
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
==========================
|