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