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 |
#1
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbers string
Dear all,
I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#2
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbersstring
One correction on the table:
QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GGAAGG 1 GG 2 GAAG 1 AG 3 GAG 2 GAG 2 GG 2 AA 1 On 14 abr, 11:54, Luciano Paulino da Silva wrote: Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes * * * * * * Number * * * * *Repeats * * * * Number QGAGGAAGGAGQ * *1 * * * * * * * * * * * GA * * * * * * * * * * *3 GAG * * * * * * * * * * * * * * 2 * * * * * * * * * * * AG * * * * * * * * * * *3 GG * * * * * * * * * * * * * * *2 * * * * * * * * * * * GAG * * * * * * * * * * 2 AA * * * * * * * * * * * * * * *1 * * * * * * * * * * * AA * * * * * * * * * * *1 * * * * * * * * * * * * * * * * * * * * * * * * * * * * GG * * * * * * * * * * *2 Somebody could help me? Thanks in advance, Luciano |
#3
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbers string
Luciano,
Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in message ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#4
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbers string
Oops, forgot the other part:
=IF(A1=A2,"It's a palindrome","It's not") where A2 has the function that I posted. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in message ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#5
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbersstring
Dear Bernie,
Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes * * * * Number Repeats Number QGAGGAAGGAGQ 1 * * * * GA * * * * 3 GAG * * * * * * * * 2 * * * * AG * * * * 3 GG * * * * * * * * 2 * * * * GAG * * * * 2 AA * * * * * * * * 1 * * * * AA * * * * 1 * * * * * * * * * * * * GG * * * * 2 Somebody could help me? Thanks in advance, Luciano |
#6
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbers string
OK, I'm sorry - I misunderstood.
There are something like SUM from 1 to LEN - 1 possible substrings: starting from every letter position, for lenghts of 2 to LEN - letter position. Say you have a 6 letter string AABBCC You would need to check for AA AAB AABB AABBC AABBCC AB ABB ABBC ABBCC BB BBC BBCC BC BCC CC Copy this code into a codemodule: Function Palindromes(strBig As String) As Variant Dim FoundPals() As String Dim PalCount As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim PalExists As Boolean PalCount = 1 ReDim FoundPals(1 To 2) For i = 1 To Len(strBig) - 1 For j = 2 To Len(strBig) - i + 1 If isPal(Mid(strBig, i, j)) Then If PalCount = 1 Then FoundPals(2) = Mid(strBig, i, j) PalCount = 2 Else PalExists = False For k = 2 To UBound(FoundPals) If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True Next k If Not PalExists Then ReDim Preserve FoundPals(1 To PalCount + 1) FoundPals(PalCount + 1) = Mid(strBig, i, j) PalCount = PalCount + 1 End If End If End If Next j Next i FoundPals(1) = PalCount - 1 & " palindromes found" Palindromes = Application.Transpose(FoundPals) End Function Function isPal(strPal As String) As Boolean Dim i As Integer Dim strTemp As String isPal = False For i = Len(strPal) To 1 Step -1 strTemp = strTemp & Mid(strPal, i, 1) Next i isPal = (strPal = strTemp) End Function Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in message ... Dear Bernie, Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#7
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbersstring
Dear Bernie,
Thank you very much for your suggestion. In addition to the number of palindromes detected for a given string, I need a list of how many times all of them are detected (e.g. for a string BAAAB, occurs two palindromes of the type AA and one palindrome BAAAB). This list is very important to me. BAAAB 3 palindromes found AA 2 palindromes found BAAAB 1 palindrome found In addition, to the same string I need to count the number of repeats. In this example we would have: BAAAB 2 repeats found AA 2 repeats found Thanks in advance, Luciano On 14 abr, 15:21, "Bernie Deitrick" deitbe @ consumer dot org wrote: OK, I'm sorry - I misunderstood. There are something like SUM from 1 to LEN - 1 *possible substrings: starting from every letter position, for lenghts of 2 to LEN - letter position. Say you have a 6 letter string AABBCC You would need to check for AA AAB AABB AABBC AABBCC AB ABB ABBC ABBCC BB BBC BBCC BC BCC CC Copy this code into a codemodule: Function Palindromes(strBig As String) As Variant Dim FoundPals() As String Dim PalCount As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim PalExists As Boolean PalCount = 1 ReDim FoundPals(1 To 2) For i = 1 To Len(strBig) - 1 * *For j = 2 To Len(strBig) - i + 1 * * * If isPal(Mid(strBig, i, j)) Then * * * * *If PalCount = 1 Then * * * * * * FoundPals(2) = Mid(strBig, i, j) * * * * * * PalCount = 2 * * * * *Else * * * * * * PalExists = False * * * * * * For k = 2 To UBound(FoundPals) * * * * * * * *If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True * * * * * * Next k * * * * * * If Not PalExists Then * * * * * * * *ReDim Preserve FoundPals(1 To PalCount + 1) * * * * * * * *FoundPals(PalCount + 1) = Mid(strBig, i, j) * * * * * * * *PalCount = PalCount + 1 * * * * * * End If * * * * *End If * * * End If * *Next j Next i FoundPals(1) = PalCount - 1 & " palindromes found" Palindromes = Application.Transpose(FoundPals) End Function Function isPal(strPal As String) As Boolean Dim i As Integer Dim strTemp As String isPal = False For i = Len(strPal) To 1 Step -1 * *strTemp = strTemp & Mid(strPal, i, 1) Next i isPal = (strPal = strTemp) End Function Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear Bernie, Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#8
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbers string
You can then use this formula in B3 to count the number of occurrences in
the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) Copy down to match the list in column A. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in message ... Dear Bernie, Thank you very much for your suggestion. In addition to the number of palindromes detected for a given string, I need a list of how many times all of them are detected (e.g. for a string BAAAB, occurs two palindromes of the type AA and one palindrome BAAAB). This list is very important to me. BAAAB 3 palindromes found AA 2 palindromes found BAAAB 1 palindrome found In addition, to the same string I need to count the number of repeats. In this example we would have: BAAAB 2 repeats found AA 2 repeats found Thanks in advance, Luciano On 14 abr, 15:21, "Bernie Deitrick" deitbe @ consumer dot org wrote: OK, I'm sorry - I misunderstood. There are something like SUM from 1 to LEN - 1 possible substrings: starting from every letter position, for lenghts of 2 to LEN - letter position. Say you have a 6 letter string AABBCC You would need to check for AA AAB AABB AABBC AABBCC AB ABB ABBC ABBCC BB BBC BBCC BC BCC CC Copy this code into a codemodule: Function Palindromes(strBig As String) As Variant Dim FoundPals() As String Dim PalCount As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim PalExists As Boolean PalCount = 1 ReDim FoundPals(1 To 2) For i = 1 To Len(strBig) - 1 For j = 2 To Len(strBig) - i + 1 If isPal(Mid(strBig, i, j)) Then If PalCount = 1 Then FoundPals(2) = Mid(strBig, i, j) PalCount = 2 Else PalExists = False For k = 2 To UBound(FoundPals) If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True Next k If Not PalExists Then ReDim Preserve FoundPals(1 To PalCount + 1) FoundPals(PalCount + 1) = Mid(strBig, i, j) PalCount = PalCount + 1 End If End If End If Next j Next i FoundPals(1) = PalCount - 1 & " palindromes found" Palindromes = Application.Transpose(FoundPals) End Function Function isPal(strPal As String) As Boolean Dim i As Integer Dim strTemp As String isPal = False For i = Len(strPal) To 1 Step -1 strTemp = strTemp & Mid(strPal, i, 1) Next i isPal = (strPal = strTemp) End Function Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear Bernie, Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#9
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbersstring
Dear Bernie,
In addition to these comments, I have tested your code and it is working fine to detect palindromes despite it is very slow. I it normal? I was testing a string of no more than 2000 characters. Thanks in advance, Luciano On 14 abr, 16:36, Luciano Paulino da Silva wrote: Dear Bernie, Thank you very much for your suggestion. In addition to the number of palindromes detected for a given string, I need a list of how many times all of them are detected (e.g. for a string BAAAB, occurs two palindromes of the type AA and one palindrome BAAAB). This list is very important to me. BAAAB 3 palindromes found AA * * * * *2 palindromes found BAAAB * 1 palindrome found In addition, to the same string I need to count the number of repeats. In this example we would have: BAAAB 2 repeats found AA * * * * *2 repeats found Thanks in advance, Luciano On 14 abr, 15:21, "Bernie Deitrick" deitbe @ consumer dot org wrote: OK, I'm sorry - I misunderstood. There are something like SUM from 1 to LEN - 1 *possible substrings: starting from every letter position, for lenghts of 2 to LEN - letter position. Say you have a 6 letter string AABBCC You would need to check for AA AAB AABB AABBC AABBCC AB ABB ABBC ABBCC BB BBC BBCC BC BCC CC Copy this code into a codemodule: Function Palindromes(strBig As String) As Variant Dim FoundPals() As String Dim PalCount As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim PalExists As Boolean PalCount = 1 ReDim FoundPals(1 To 2) For i = 1 To Len(strBig) - 1 * *For j = 2 To Len(strBig) - i + 1 * * * If isPal(Mid(strBig, i, j)) Then * * * * *If PalCount = 1 Then * * * * * * FoundPals(2) = Mid(strBig, i, j) * * * * * * PalCount = 2 * * * * *Else * * * * * * PalExists = False * * * * * * For k = 2 To UBound(FoundPals) * * * * * * * *If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True * * * * * * Next k * * * * * * If Not PalExists Then * * * * * * * *ReDim Preserve FoundPals(1 To PalCount + 1) * * * * * * * *FoundPals(PalCount + 1) = Mid(strBig, i, j) * * * * * * * *PalCount = PalCount + 1 * * * * * * End If * * * * *End If * * * End If * *Next j Next i FoundPals(1) = PalCount - 1 & " palindromes found" Palindromes = Application.Transpose(FoundPals) End Function Function isPal(strPal As String) As Boolean Dim i As Integer Dim strTemp As String isPal = False For i = Len(strPal) To 1 Step -1 * *strTemp = strTemp & Mid(strPal, i, 1) Next i isPal = (strPal = strTemp) End Function Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in .... Dear Bernie, Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
#10
|
|||
|
|||
Macro for detect palindromes and repeats in letters/numbersstring
I could not understand the sentence:
Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) I have tried that and I do not have the list of palindromes detected in the string. What would it be the result of such formula? Thanks in advance, Luciano On 14 abr, 16:53, "Bernie Deitrick" deitbe @ consumer dot org wrote: You can then use this formula in B3 to count the number of occurrences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) Copy down to match the list in column A. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear Bernie, Thank you very much for your suggestion. In addition to the number of palindromes detected for a given string, I need a list of how many times all of them are detected (e.g. for a string BAAAB, occurs two palindromes of the type AA and one palindrome BAAAB). This list is very important to me. BAAAB 3 palindromes found AA * * * * *2 palindromes found BAAAB * 1 palindrome found In addition, to the same string I need to count the number of repeats. In this example we would have: BAAAB 2 repeats found AA * * * * *2 repeats found Thanks in advance, Luciano On 14 abr, 15:21, "Bernie Deitrick" deitbe @ consumer dot org wrote: OK, I'm sorry - I misunderstood. There are something like SUM from 1 to LEN - 1 possible substrings: starting from every letter position, for lenghts of 2 to LEN - letter position. Say you have a 6 letter string AABBCC You would need to check for AA AAB AABB AABBC AABBCC AB ABB ABBC ABBCC BB BBC BBCC BC BCC CC Copy this code into a codemodule: Function Palindromes(strBig As String) As Variant Dim FoundPals() As String Dim PalCount As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim PalExists As Boolean PalCount = 1 ReDim FoundPals(1 To 2) For i = 1 To Len(strBig) - 1 For j = 2 To Len(strBig) - i + 1 If isPal(Mid(strBig, i, j)) Then If PalCount = 1 Then FoundPals(2) = Mid(strBig, i, j) PalCount = 2 Else PalExists = False For k = 2 To UBound(FoundPals) If FoundPals(k) = Mid(strBig, i, j) Then PalExists = True Next k If Not PalExists Then ReDim Preserve FoundPals(1 To PalCount + 1) FoundPals(PalCount + 1) = Mid(strBig, i, j) PalCount = PalCount + 1 End If End If End If Next j Next i FoundPals(1) = PalCount - 1 & " palindromes found" Palindromes = Application.Transpose(FoundPals) End Function Function isPal(strPal As String) As Boolean Dim i As Integer Dim strTemp As String isPal = False For i = Len(strPal) To 1 Step -1 strTemp = strTemp & Mid(strPal, i, 1) Next i isPal = (strPal = strTemp) End Function Then select cells A2 through A100 or so, type =PALINDROMES(A1) and press Ctrl-Shift-Enter Cell A2 will tell you haow many palindromes were found, so you may need to expand how many cells you select initially before entering the formula. You can then use this formula in B3 to count the number of occurences in the string. =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear Bernie, Thank you very much for your interest helping me. However, your code only show the inverse of a string present for a given cell. I need a code able to list and count the number of times that appear all palindromes that could be detect from a given string. Did you understand now? Thanks in advance, Luciano On 14 abr, 13:59, "Bernie Deitrick" deitbe @ consumer dot org wrote: Luciano, Copy this code and put it into a codemodule of your workbook. Function Palindrome(strWord As String) As String Dim i As Integer For i = Len(strWord) To 1 Step -1 Palindrome = Palindrome & Mid(strWord, i, 1) Next i End Function Then use it like this in a cell. =PALINDROME(A1) Then, for the repeat counts, use this formula =(LEN($A$1)-LEN(SUBSTITUTE($A$1,A3,"")))/LEN(A3) where A3 has the string that you are looking for. HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could be listed bellow cells B2 and D2, respectively, as the following small example: QGAGGAAGGAGQ Palindromes Number Repeats Number QGAGGAAGGAGQ 1 GA 3 GAG 2 AG 3 GG 2 GAG 2 AA 1 AA 1 GG 2 Somebody could help me? Thanks in advance, Luciano |
Thread Tools | |
Display Modes | |
|
|