Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

One correction on the table:
QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GGAAGG 1 GG 2
GAAG 1 AG 3
GAG 2 GAG 2
GG 2
AA 1

On 14 abr, 11:54, Luciano Paulino da Silva
wrote:
Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes * * * * * * Number * * * * *Repeats * * * * Number
QGAGGAAGGAGQ * *1 * * * * * * * * * * * GA * * * * * * * * * * *3
GAG * * * * * * * * * * * * * * 2 * * * * * * * * * * * AG * * * * * * * * * * *3
GG * * * * * * * * * * * * * * *2 * * * * * * * * * * * GAG * * * * * * * * * * 2
AA * * * * * * * * * * * * * * *1 * * * * * * * * * * * AA * * * * * * * * * * *1
* * * * * * * * * * * * * * * * * * * * * * * * * * * * GG * * * * * * * * * * *2

Somebody could help me?
Thanks in advance,
Luciano

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function


Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in message
...
Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Oops, forgot the other part:

=IF(A1=A2,"It's a palindrome","It's not")

where A2 has the function that I posted.

HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in message
...
Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes * * * * Number Repeats Number
QGAGGAAGGAGQ 1 * * * * GA * * * * 3
GAG * * * * * * * * 2 * * * * AG * * * * 3
GG * * * * * * * * 2 * * * * GAG * * * * 2
AA * * * * * * * * 1 * * * * AA * * * * 1
* * * * * * * * * * * * GG * * * * 2

Somebody could help me?
Thanks in advance,
Luciano

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings: starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function


Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA 2 palindromes found
BAAAB 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA 2 repeats found


Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 *possible substrings: starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
* *For j = 2 To Len(strBig) - i + 1
* * * If isPal(Mid(strBig, i, j)) Then
* * * * *If PalCount = 1 Then
* * * * * * FoundPals(2) = Mid(strBig, i, j)
* * * * * * PalCount = 2
* * * * *Else
* * * * * * PalExists = False
* * * * * * For k = 2 To UBound(FoundPals)
* * * * * * * *If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
* * * * * * Next k
* * * * * * If Not PalExists Then
* * * * * * * *ReDim Preserve FoundPals(1 To PalCount + 1)
* * * * * * * *FoundPals(PalCount + 1) = Mid(strBig, i, j)
* * * * * * * *PalCount = PalCount + 1
* * * * * * End If
* * * * *End If
* * * End If
* *Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
* *strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

You can then use this formula in B3 to count the number of occurrences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA 2 palindromes found
BAAAB 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA 2 repeats found


Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Dear Bernie,
In addition to these comments, I have tested your code and it is
working fine to detect palindromes despite it is very slow. I it
normal? I was testing a string of no more than 2000 characters.
Thanks in advance,
Luciano

On 14 abr, 16:36, Luciano Paulino da Silva
wrote:
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA * * * * *2 palindromes found
BAAAB * 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA * * * * *2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 *possible substrings: starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
* *For j = 2 To Len(strBig) - i + 1
* * * If isPal(Mid(strBig, i, j)) Then
* * * * *If PalCount = 1 Then
* * * * * * FoundPals(2) = Mid(strBig, i, j)
* * * * * * PalCount = 2
* * * * *Else
* * * * * * PalExists = False
* * * * * * For k = 2 To UBound(FoundPals)
* * * * * * * *If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
* * * * * * Next k
* * * * * * If Not PalExists Then
* * * * * * * *ReDim Preserve FoundPals(1 To PalCount + 1)
* * * * * * * *FoundPals(PalCount + 1) = Mid(strBig, i, j)
* * * * * * * *PalCount = PalCount + 1
* * * * * * End If
* * * * *End If
* * * End If
* *Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
* *strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ....
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano


On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
You can then use this formula in B3 to count the number of occurrences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA * * * * *2 palindromes found
BAAAB * 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA * * * * *2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

I understood the first part. However, the formula =(LEN($A$1)-LEN
(SUBSTITUTE($A$1,A3,"")))/LEN(A3) did not work to show the number of
palindromes for a givem type.

On 14 abr, 17:13, Luciano Paulino da Silva
wrote:
I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano

On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

You can then use this formula in B3 to count the number of occurrences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ....
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA * * * * *2 palindromes found
BAAAB * 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA * * * * *2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Sorry, it was a language problem, sorry :)

On 14 abr, 17:13, Luciano Paulino da Silva
wrote:
I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano

On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

You can then use this formula in B3 to count the number of occurrences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ....
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA * * * * *2 palindromes found
BAAAB * 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA * * * * *2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Dear Bernie,
Thank you very much! The palindrome code and formula are working fine.
Howerver, for strigs bigger than 1000 letters it is very slow.
Do you have any idea abou some code in order to detect the number of
repeats for a givem string using exactly the same strategy.
For example:
AABBAABBCCAACC
3 repeats detected
AA 3
BB 2
CC 2

Thank you very much again,
Luciano

On 14 abr, 17:23, Luciano Paulino da Silva
wrote:
Sorry, it was a language problem, sorry :)

On 14 abr, 17:13, Luciano Paulino da Silva

wrote:
I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano

On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

You can then use this formula in B3 to count the number of occurrences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA * * * * *2 palindromes found
BAAAB * 1 palindrome found

In addition, to the same string I need to count the number of repeats..
In this example we would have:
BAAAB
2 repeats found
AA * * * * *2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Luciano,

For a 2000 character string, the code needs to check about 1 million (1E6)
combinations, of strings up to 2000 characters long, so it will be slow.

Bernie
MS Excel MVP




"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
In addition to these comments, I have tested your code and it is
working fine to detect palindromes despite it is very slow. I it
normal? I was testing a string of no more than 2000 characters.
Thanks in advance,
Luciano

On 14 abr, 16:36, Luciano Paulino da Silva
wrote:
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA 2 palindromes found
BAAAB 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA 2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may need
to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could be listed bellow cells B2 and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Luciano,

Does the formula approach for counting the strings not work? It can be done
in code, using a dictionary or 2D array approach, but using the formula
should be very quick.

Bernie


"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
Thank you very much! The palindrome code and formula are working fine.
Howerver, for strigs bigger than 1000 letters it is very slow.
Do you have any idea abou some code in order to detect the number of
repeats for a givem string using exactly the same strategy.
For example:
AABBAABBCCAACC
3 repeats detected
AA 3
BB 2
CC 2

Thank you very much again,
Luciano

On 14 abr, 17:23, Luciano Paulino da Silva
wrote:
Sorry, it was a language problem, sorry :)

On 14 abr, 17:13, Luciano Paulino da Silva

wrote:
I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano

On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

You can then use this formula in B3 to count the number of occurrences
in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA 2 palindromes found
BAAAB 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA 2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of
occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given
string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote
in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either
direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could
be
listed bellow A2 and C2, respectively; and that the number of
times
that they appear in the sentence could be listed bellow cells B2
and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

On 14 abr, 20:06, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Does the formula approach for counting the strings not work? It can be done
in code, using a dictionary or 2D array approach, but using the formula
should be very quick.

Bernie

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
Thank you very much! The palindrome code and formula are working fine.
Howerver, for strigs bigger than 1000 letters it is very slow.
Do you have any idea abou some code in order to detect the number of
repeats for a givem string using exactly the same strategy.
For example:
AABBAABBCCAACC
3 repeats detected
AA * * * * * * * * * * * * * * * *3
BB * * * * * * * * * * * * * * * *2
CC * * * * * * * * * * * * * * * *2

Thank you very much again,
Luciano

On 14 abr, 17:23, Luciano Paulino da Silva

wrote:
Sorry, it was a language problem, sorry :)

On 14 abr, 17:13, Luciano Paulino da Silva

wrote:
I could not understand the sentence:

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of occurences
in the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

I have tried that and I do not have the list of palindromes detected
in the string.
What would it be the result of such formula?
Thanks in advance,
Luciano

On 14 abr, 16:53, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

You can then use this formula in B3 to count the number of occurrences
in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

Copy down to match the list in column A.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your suggestion.
In addition to the number of palindromes detected for a given string,
I need a list of how many times all of them are detected (e.g. for a
string BAAAB, occurs two palindromes of the type AA and one palindrome
BAAAB).
This list is very important to me.

BAAAB
3 palindromes found
AA 2 palindromes found
BAAAB 1 palindrome found

In addition, to the same string I need to count the number of repeats.
In this example we would have:
BAAAB
2 repeats found
AA 2 repeats found

Thanks in advance,
Luciano

On 14 abr, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK, I'm sorry - I misunderstood.

There are something like SUM from 1 to LEN - 1 possible substrings:
starting from every letter
position, for lenghts of 2 to LEN - letter position.

Say you have a 6 letter string

AABBCC

You would need to check for

AA
AAB
AABB
AABBC
AABBCC
AB
ABB
ABBC
ABBCC
BB
BBC
BBCC
BC
BCC
CC

Copy this code into a codemodule:

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(2) = Mid(strBig, i, j)
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals)
If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To PalCount + 1)
FoundPals(PalCount + 1) = Mid(strBig, i, j)
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1) = PalCount - 1 & " palindromes found"
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

Then select cells A2 through A100 or so, type =PALINDROMES(A1) and
press
Ctrl-Shift-Enter

Cell A2 will tell you haow many palindromes were found, so you may
need to
expand how many cells you
select initially before entering the formula.

You can then use this formula in B3 to count the number of
occurences in
the string.

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...
Dear Bernie,
Thank you very much for your interest helping me.
However, your code only show the inverse of a string present for a
given cell. I need a code able to list and count the number of times
that appear all palindromes that could be detect from a given
string.
Did you understand now?
Thanks in advance,
Luciano

On 14 abr, 13:59, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Copy this code and put it into a codemodule of your workbook.

Function Palindrome(strWord As String) As String
Dim i As Integer
For i = Len(strWord) To 1 Step -1
Palindrome = Palindrome & Mid(strWord, i, 1)
Next i
End Function

Then use it like this in a cell.

=PALINDROME(A1)

Then, for the repeat counts, use this formula

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3)

where A3 has the string that you are looking for.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote
in
...

Dear all,
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either
direction)
and repeats (sequences of letters or numbers which are repeating
atleast 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 palindromes and repeats could
be
listed bellow A2 and C2, respectively; and that the number of
times
that they appear in the sentence could be listed bellow cells B2
and
D2, respectively, as the following small example:

QGAGGAAGGAGQ
Palindromes Number Repeats Number
QGAGGAAGGAGQ 1 GA 3
GAG 2 AG 3
GG 2 GAG 2
AA 1 AA 1
GG 2

Somebody could help me?
Thanks in advance,
Luciano

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function



"Luciano Paulino da Silva" wrote in message
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano


On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
* *For j = 2 To Len(strBig) - i + 1
* * * If isPal(Mid(strBig, i, j)) Then
* * * * *If PalCount = 1 Then
* * * * * * FoundPals(1, 2) = Mid(strBig, i, j)
* * * * * * FoundPals(2, 2) = 1
* * * * * * PalCount = 2
* * * * *Else
* * * * * * PalExists = False
* * * * * * For k = 2 To UBound(FoundPals, 2)
* * * * * * * *If FoundPals(1, k) = Mid(strBig, i, j) Then
* * * * * * * *PalExists = True
* * * * * * * *FoundPals(2, k) = FoundPals(2, k) + 1
* * * * * * * *End If
* * * * * * Next k
* * * * * * If Not PalExists Then
* * * * * * * *ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
* * * * * * * *FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
* * * * * * * *FoundPals(2, PalCount + 1) = 1
* * * * * * * *PalCount = PalCount + 1
* * * * * * End If
* * * * *End If
* * * End If
* *Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
* *strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in ...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie


"Luciano Paulino da Silva" wrote in message
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano


On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in ...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in message
...
OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35, Luciano Paulino da Silva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

Yes, it is exactly what I want. Is it possible to you modify the code
for me?
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

I'm not clear on what you want - only those that appear more than once?
What do you want with the ABBA and the AABBAA and the BAAB, all of which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Change the code to that given below to include the count of the number of
repeats, then select A2:B??? before entering =PALINDROMES(A1) using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats using
the same strategy?
Thanks in advance,
Luciano

Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function


"Luciano Paulino da Silva" wrote in message
...
It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35, Luciano Paulino da Silva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"Luciano Paulino da Silva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"Luciano Paulino da Silva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano

Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Same drill: *Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in ...

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"LucianoPaulinodaSilva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"LucianoPaulinodaSilva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"LucianoPaulinodaSilva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano

Luciano,

Did you check your typing?
Did you put it into a standard codemodule and not a worksheet codemodule?
Did you copy the code into a codemodule in the correct workbook?

It worked for me - let me know if you can't get it to work, and I will send
you a working version, if you gmail account is not a spoof.

Bernie

"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"LucianoPaulinodaSilva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be
listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"LucianoPaulinodaSilva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this
moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"LucianoPaulinodaSilva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano

And, last but not least, did you copy the code and paste it into your
codemodule? That is what I meant by "Same drill" copy the code below my
signature, paste it into a codemodule, etc.

Sorry for being too brief in my instructions.

Bernie


"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"LucianoPaulinodaSilva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be
listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"LucianoPaulinodaSilva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this
moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"LucianoPaulinodaSilva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano

Dear Bernie,
Thank you very much for your attention.
Yes, I have performed all these operations, but I had created a new
sheet into the same file that it was the palindromes code.
Now, I created a new file and it is running. It is appearing a box
showing all detected repeats for which I have to press OK in order to
step the next one. Can I put it out?
Thank you very much for all your help,
Luciano

On 15 abr, 20:57, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
And, last but not least, did you copy the code and paste it into your
codemodule? *That is what I meant by "Same drill" copy the code below my
signature, paste it into a codemodule, etc.

Sorry for being too brief in my instructions.

Bernie

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano

On 15 abr, 19:29, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"LucianoPaulinodaSilva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be
listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"LucianoPaulinodaSilva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this
moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"LucianoPaulinodaSilva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano

Dear Bernie,
Thank you very much!
It is working. However, I have to sum the values bellow B3 in order to
get the total numbers of repeats observed that is different from the
total number of different repeats. Have you some idea why the sum
comand did not work?
Thanks in advance,
Luciano


On 15 abr, 20:41, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Luciano,

Did you check your typing?
Did you put it into a standard codemodule and not a worksheet codemodule?
Did you copy the code into a codemodule in the correct workbook?

It worked for me - let me know if you can't get it to work, and I will send
you a working version, if you gmail account is not a spoof.

Bernie

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano

On 15 abr, 19:29, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do
so.

HTH,
Bernie
MS Excel MVP

"LucianoPaulinodaSilva" wrote in
...

OK!
It is the following:
My first problem that was related to palindromes detection your
suggestion was absolutely perfect.
Now, my second problem is relate to detect repeats (sequences of
letters or numbers which are repeating at least twice within a
string)
in the same sequences. Of course that some repeats are also
palindromes. But in this case the listing and counting must be only
of
repeats consisting of two or more letters. For example, the string
that you listed bellow has only one repeat (AA) that should be
listed
in this case. It appears 2 times in the string.
Didi you understand?
Thanks in advance,
Luciano

On 14 abr, 23:34, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Luciano,

I'm not clear on what you want - only those that appear more than
once?
What do you want with the ABBA and the AABBAA and the BAAB, all of
which
only appear once?

Bernie

"LucianoPaulinodaSilva" wrote in
...
Bernie,
It is possible that I have not explained very well. At this
moment,
I`m needing a code that detect the repeats for a givem string
instead
to detect palindromes. For example, the string AABBAABB has two
repeats (AA and BB), and they appear 2 times.
Do you understand it now?
Thanks in advance,
Luciano

On 14 abr, 21:16, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Luciano,

Change the code to that given below to include the count of the
number of
repeats, then select A2:B??? before entering =PALINDROMES(A1)
using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Function Palindromes(strBig As String) As Variant
Dim FoundPals() As String
Dim PalCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim PalExists As Boolean

PalCount = 1
ReDim FoundPals(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
If isPal(Mid(strBig, i, j)) Then
If PalCount = 1 Then
FoundPals(1, 2) = Mid(strBig, i, j)
FoundPals(2, 2) = 1
PalCount = 2
Else
PalExists = False
For k = 2 To UBound(FoundPals, 2)
If FoundPals(1, k) = Mid(strBig, i, j) Then
PalExists = True
FoundPals(2, k) = FoundPals(2, k) + 1
End If
Next k
If Not PalExists Then
ReDim Preserve FoundPals(1 To 2, 1 To PalCount + 1)
FoundPals(1, PalCount + 1) = Mid(strBig, i, j)
FoundPals(2, PalCount + 1) = 1
PalCount = PalCount + 1
End If
End If
End If
Next j
Next i

FoundPals(1, 1) = "Palindromes found:"
FoundPals(2, 1) = PalCount - 1
Palindromes = Application.Transpose(FoundPals)

End Function
Function isPal(strPal As String) As Boolean
Dim i As Integer
Dim strTemp As String
isPal = False
For i = Len(strPal) To 1 Step -1
strTemp = strTemp & Mid(strPal, i, 1)
Next i
isPal = (strPal = strTemp)
End Function

"LucianoPaulinodaSilva" wrote
in
...
Bernie,
Yes, it is working fine. Thank you very much.
During some situations it is very slow, but I understand that
there
are a lot of possibilities to test.
Do you know how some code could be used to check about repeats
using
the same strategy?
Thanks in advance,
Luciano