Hi macropod,
In my case I will not looking for a specific repeat. My intention is
list all subsequences that are present in a full string in A1 (e.g.
for the string AADFDGAAV, the string AA appear 2 times and therefore
should be counted. At the right side of the subsequences (repeats)
listed it will be necessary to figure out the number of times that
each one of them appear. And in another cell the number of different
repeats that could be detected.
Did you understand?
Thanks in advance,
Luciano

On 15 abr, 00:23, "macropod" wrote:
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 ...
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