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
  #21  
Old April 15th, 2009, 01:49 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 for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in message
...
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




  #22  
Old April 15th, 2009, 04: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,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:
OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

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


  #23  
Old April 15th, 2009, 05: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/numbersstring

It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35, Luciano Paulino da Silva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do so.


HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in ...


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


  #24  
Old April 15th, 2009, 11:27 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

Yes, it is exactly what I want. Is it possible to you modify the code
for me?
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:
OK for for palindromes, but the string that you posted

AABBAABB

has the repeats

AA
AAB
AABB
AB
ABB
BB

not just AA and BB...

If that is what you actually want, then I will modify the code to do so.

HTH,
Bernie
MS Excel MVP

"Luciano Paulino da Silva" wrote in ...

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


  #25  
Old April 15th, 2009, 11:29 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,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function


"Luciano Paulino da Silva" wrote in message
...
It is exactly that I want.
Thank you,
Luciano

On 15 abr, 12:35, Luciano Paulino da Silva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano

On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:

OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"Luciano Paulino da Silva" wrote in
...


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



  #26  
Old April 16th, 2009, 12:24 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

Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" deitbe @ consumer dot org wrote:
Luciano,

Same drill: *Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in ...

It is exactly that I want.
Thank you,
Luciano


On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano


On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:


OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"LucianoPaulinodaSilva" wrote in
...


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


"LucianoPaulinodaSilva" 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


"LucianoPaulinodaSilva" 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


  #27  
Old April 16th, 2009, 12:41 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,

Did you check your typing?
Did you put it into a standard codemodule and not a worksheet codemodule?
Did you copy the code into a codemodule in the correct workbook?

It worked for me - let me know if you can't get it to work, and I will send
you a working version, if you gmail account is not a spoof.

Bernie

"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" deitbe @ consumer dot org wrote:
Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano


On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano


On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:


OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"LucianoPaulinodaSilva" wrote in
...


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


"LucianoPaulinodaSilva" 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


"LucianoPaulinodaSilva" 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


  #28  
Old April 16th, 2009, 12:57 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

And, last but not least, did you copy the code and paste it into your
codemodule? That is what I meant by "Same drill" copy the code below my
signature, paste it into a codemodule, etc.

Sorry for being too brief in my instructions.

Bernie


"Luciano Paulino da Silva" wrote in message
...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano


On 15 abr, 19:29, "Bernie Deitrick" deitbe @ consumer dot org wrote:
Luciano,

Same drill: Enter

=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

Option Explicit

Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean

RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i

FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)

End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function

"LucianoPaulinodaSilva" wrote in
...

It is exactly that I want.
Thank you,
Luciano


On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano


On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:


OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"LucianoPaulinodaSilva" wrote in
...


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


"LucianoPaulinodaSilva" 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


"LucianoPaulinodaSilva" 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


  #29  
Old April 16th, 2009, 02:26 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

Dear Bernie,
Thank you very much for your attention.
Yes, I have performed all these operations, but I had created a new
sheet into the same file that it was the palindromes code.
Now, I created a new file and it is running. It is appearing a box
showing all detected repeats for which I have to press OK in order to
step the next one. Can I put it out?
Thank you very much for all your help,
Luciano

On 15 abr, 20:57, "Bernie Deitrick" deitbe @ consumer dot org wrote:
And, last but not least, did you copy the code and paste it into your
codemodule? *That is what I meant by "Same drill" copy the code below my
signature, paste it into a codemodule, etc.

Sorry for being too brief in my instructions.

Bernie

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano

On 15 abr, 19:29, "Bernie Deitrick" deitbe @ consumer dot org wrote:

Luciano,


Same drill: Enter


=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.


HTH,
Bernie
MS Excel MVP


Option Explicit


Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean


RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i


FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)


End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function


"LucianoPaulinodaSilva" wrote in
...


It is exactly that I want.
Thank you,
Luciano


On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano


On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:


OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"LucianoPaulinodaSilva" wrote in
...


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


"LucianoPaulinodaSilva" 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


"LucianoPaulinodaSilva" 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


  #30  
Old April 16th, 2009, 11:41 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

Dear Bernie,
Thank you very much!
It is working. However, I have to sum the values bellow B3 in order to
get the total numbers of repeats observed that is different from the
total number of different repeats. Have you some idea why the sum
comand did not work?
Thanks in advance,
Luciano


On 15 abr, 20:41, "Bernie Deitrick" deitbe @ consumer dot org wrote:
Luciano,

Did you check your typing?
Did you put it into a standard codemodule and not a worksheet codemodule?
Did you copy the code into a codemodule in the correct workbook?

It worked for me - let me know if you can't get it to work, and I will send
you a working version, if you gmail account is not a spoof.

Bernie

"Luciano Paulino da Silva" wrote in ...
Dear Bernie,
I tried a lot to run the code but it is not working. Have you some
idea about what could it be happening?
I just receive the error: #NAME! for all cells in the interval.
Thanks in advance,
Luciano

On 15 abr, 19:29, "Bernie Deitrick" deitbe @ consumer dot org wrote:

Luciano,


Same drill: Enter


=REPEATS(A1) into cells A2:B??? using Ctrl-Shift-Enter.


HTH,
Bernie
MS Excel MVP


Option Explicit


Function Repeats(strBig As String) As Variant
Dim FoundRpts() As String
Dim RptCount As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RptExists As Boolean


RptCount = 1
ReDim FoundRpts(1 To 2, 1 To 2)
For i = 1 To Len(strBig) - 1
For j = 2 To Len(strBig) - i + 1
x = isRpt(Mid(strBig, i, j), strBig, i + 1)
If x 0 Then
If RptCount = 1 Then
FoundRpts(1, 2) = Mid(strBig, i, j)
FoundRpts(2, 2) = x
RptCount = 2
Else
RptExists = False
For k = 2 To UBound(FoundRpts, 2)
If FoundRpts(1, k) = Mid(strBig, i, j) Then
RptExists = True
End If
Next k
If Not RptExists Then
ReDim Preserve FoundRpts(1 To 2, 1 To RptCount + 1)
FoundRpts(1, RptCount + 1) = Mid(strBig, i, j)
FoundRpts(2, RptCount + 1) = x
MsgBox FoundRpts(1, RptCount + 1) & " " & x
RptCount = RptCount + 1
End If
End If
End If
Next j
Next i


FoundRpts(1, 1) = "Repeats found:"
FoundRpts(2, 1) = RptCount - 1
Repeats = Application.Transpose(FoundRpts)


End Function
Function isRpt(strRpt As String, strPar As String, i As Integer) As
Integer
isRpt = 0
If InStr(i, strPar, strRpt) 0 Then
isRpt = (Len(Mid(strPar, i, Len(strPar))) - _
Len(Replace(Mid(strPar, i, Len(strPar)), strRpt, ""))) / _
Len(strRpt) + 1
End If
End Function


"LucianoPaulinodaSilva" wrote in
...


It is exactly that I want.
Thank you,
Luciano


On 15 abr, 12:35,LucianoPaulinodaSilva
wrote:
Dear Bernie,
Yes, you are correct that there are other repeats in this string.
Thanks in advance,
Luciano


On 15 abr, 09:49, "Bernie Deitrick" deitbe @ consumer dot org wrote:


OK for for palindromes, but the string that you posted


AABBAABB


has the repeats


AA
AAB
AABB
AB
ABB
BB


not just AA and BB...


If that is what you actually want, then I will modify the code to do
so.


HTH,
Bernie
MS Excel MVP


"LucianoPaulinodaSilva" wrote in
...


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


"LucianoPaulinodaSilva" 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


"LucianoPaulinodaSilva" 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 03:46 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.