Count the duplicate string in the cell

[tamer]

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 macro

 

[Claus Busch]

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

 

[Ron Rosenfeld]

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

 

[Spencer101]

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 th
process. But without more information on what you're actually searchin
for, it's hard to tell if it will be an issue for you

+-------------------------------------------------------------------
|Filename: tamer example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=633
+-------------------------------------------------------------------

 

[tamer]

;1606575']On Sat, 20 Oct 2012 07:11:50 +0000, tame

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 tha
the word "time" only occurs once.

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

To enter this User Defined Function (UDF), <alt-F11> opens the Visua
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) A
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 d
tha

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

thx you guy it works

Happy to help :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

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"

 

[Spencer101]

here it is

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

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 t
accomplish with this.

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[tamer]

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 t
accomplish with this.

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

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------