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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|