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
  #11  
Old April 14th, 2009, 09:21 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 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


  #12  
Old April 14th, 2009, 09:23 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

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


  #13  
Old April 14th, 2009, 09:35 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! 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


  #14  
Old April 15th, 2009, 12:05 AM 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,

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


  #15  
Old April 15th, 2009, 12:06 AM 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,

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


  #16  
Old April 15th, 2009, 12:45 AM 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

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


  #17  
Old April 15th, 2009, 01:16 AM 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,

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

  #18  
Old April 15th, 2009, 03:00 AM 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

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


  #19  
Old April 15th, 2009, 03:34 AM 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,

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


  #20  
Old April 15th, 2009, 03:57 AM 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

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


 




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 02:47 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.