Macro for detect palindromes and repeats in letters/numbers string

[Luciano Paulino da Silva]

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 Paulino da Silva]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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


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

 

[Luciano Paulino da Silva]

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

 

[Bernie Deitrick]

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

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

 

[Luciano Paulino da Silva]

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

 

[Luciano Paulino da Silva]

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

 

[Luciano Paulino da Silva]

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.

 

[Luciano Paulino da Silva]

Sorry, it was a language problem, sorry

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

 

[Luciano Paulino da Silva]

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

 

[Bernie Deitrick]

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




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

 

[Bernie Deitrick]

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


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

 

[Luciano Paulino da Silva]

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 Deitrick]

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



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 Paulino da Silva]

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

 

[Bernie Deitrick]

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


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

 

[Luciano Paulino da Silva]

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