A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Macro for detect palindromes and repeats in letters/numbers string



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2009, 03:54 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbers string

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
  #2  
Old April 14th, 2009, 05:30 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbersstring

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


  #3  
Old April 14th, 2009, 05:59 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 2,496
Default Macro for detect palindromes and repeats in letters/numbers string

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



  #4  
Old April 14th, 2009, 06:04 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 2,496
Default Macro for detect palindromes and repeats in letters/numbers string

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



  #5  
Old April 14th, 2009, 06:14 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbersstring

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


  #6  
Old April 14th, 2009, 07:21 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 2,496
Default Macro for detect palindromes and repeats in letters/numbers string

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



  #7  
Old April 14th, 2009, 08:36 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbersstring

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


  #8  
Old April 14th, 2009, 08:53 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 2,496
Default Macro for detect palindromes and repeats in letters/numbers string

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


  #9  
Old April 14th, 2009, 09:03 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbersstring

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


  #10  
Old April 14th, 2009, 09:13 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Macro for detect palindromes and repeats in letters/numbersstring

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.