Hi Luciano,

You can implement the logic fvrom the code I posted earlier via a User-Defined Function:

Public Function Repeats(BigStr As Variant, MyStr As Variant, Compare As Boolean) As Integer
Dim TmpStr As String
TmpStr = Replace(BigStr, MyStr, "", , , Compare ^ 2)
Repeats = (Len(BigStr) - Len(TmpStr)) / Len(MyStr)
End Function

If you place the function in a standard vba module, you can use a formula like:
=Repeats(A1,A2,1) or =Repeats(A1,A2,0)
to retrieve the number of times the string in A2 is found in A1. The 1/0 parameter specifies a case-sensitive or case-insensitive
match, respectively.

--
Cheers
macropod
[MVP - Microsoft Word]


"Luciano Paulino da Silva" wrote in message
...
Dear macropod,
I have tested your code, but it did not worked for my need,
Thank you anyway,
Luciano

On 14 abr, 21:32, "macropod" wrote:
Hi Luciano,

You could use code like:
Sub TestString()
Dim BigStr As String
Dim MyStr As String
Dim TmpStr As String
BigStr = "The quick brown fox jumps over the lazy dog."
MyStr = InputBox("String to Find")
TmpStr = Replace(BigStr, MyStr, "", , , vbBinaryCompare)
MsgBox "Repeats of " & MyStr & " in Test String: " & (Len(BigStr) - Len(TmpStr)) / Len(MyStr)
End Sub

Using vbBinaryCompare forces a case-sensitive test (eg "the" is not counted in a search for "The"). If you don't want the test to
be
case sensitive, use vbTextCompare instead.

--
Cheers
macropod
[MVP - Microsoft Word]

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect repeats (sequences of letters or numbers which
are repeating
at least twice within a string) in some strings containing between
20-5000 letters. Has somebody any idea how could I perform that using
an Excel macro? I would like that the string to be evaluated could be
on cell "A1" and that the detected repeats could be
listed bellow A3; and that the number of times
that they appear in the sentence could be listed bellow cells B3, as
the following small example:

QGAGGAAGGAGQ
4 Repeats detected Number
GA 3
AG 3
GAG 2
GG 2

Somebody could help me?
Thanks in advance,
Luciano