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

 

[Nozza]

One of the first UDF's I wrote beack in 2005 was a palindrome checker.
Could probably write it a little better now, but I wrote it to try to
understand how to write a UDF

Could be a start for you.

Good luck

Noz


Function IsPalindrome(Selection) As Boolean
'Returns True if cell is a palindrome

'Declarations
Dim MyStringBefore As String
Dim MyStringInProgress As String
Dim MyStringReversed As String
Dim CharacterToCheck As String
Dim i As Integer

'Check whether a range has been selected
If TypeName(Selection) <> "Range" Then Exit Function
MyStringBefore = UCase(Selection.Value)

'strip out any non-letters
For i = 1 To Len(MyStringBefore)

CharacterToCheck = Mid(MyStringBefore, i, 1)
If Asc(CharacterToCheck) >= 65 And Asc(CharacterToCheck) <= 90
Then
'found letter that is A-Z
MyStringInProgress = MyStringInProgress & CharacterToCheck
End If

Next i

'Mystringinprogress is now simply uppercase A-Z's with no spaces.

For i = Len(MyStringInProgress) To 1 Step -1
MyStringReversed = MyStringReversed & Mid(MyStringInProgress,
i, 1)
Next i

If MyStringReversed = MyStringInProgress Then
IsPalindrome = True
Else
IsPalindrome = False
End If

End Function

 

[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

correction:

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

 

[Luciano Paulino da Silva]

Thank you Noz!
I will try!
Luciano