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
0
4/14/2009 2:55:28 PM
excel 39879 articles. 2 followers. Follow

3 Replies
436 Views

Similar Articles

[PageSpeed] 23

One of the first UDF's I wrote beack in 2005 was a palindrome checker.
Could probably write it a little better now, but I wrote it to try to
understand how to write a UDF

Could be a start for you.

Good luck

Noz


Function IsPalindrome(Selection) As Boolean
'Returns True if cell is a palindrome
    
    'Declarations
    Dim MyStringBefore As String
    Dim MyStringInProgress As String
    Dim MyStringReversed As String
    Dim CharacterToCheck As String
    Dim i As Integer
    
    'Check whether a range has been selected
    If TypeName(Selection) <> "Range" Then Exit Function
    MyStringBefore = UCase(Selection.Value)
    
    'strip out any non-letters
    For i = 1 To Len(MyStringBefore)
        
        CharacterToCheck = Mid(MyStringBefore, i, 1)
        If Asc(CharacterToCheck) >= 65 And Asc(CharacterToCheck) <= 90
Then
            'found letter that is A-Z
            MyStringInProgress = MyStringInProgress & CharacterToCheck
        End If
        
    Next i
    
    'Mystringinprogress is now simply uppercase A-Z's with no spaces.
    
    For i = Len(MyStringInProgress) To 1 Step -1
        MyStringReversed = MyStringReversed & Mid(MyStringInProgress,
i, 1)
    Next i
    
    If MyStringReversed = MyStringInProgress Then
        IsPalindrome = True
    Else
        IsPalindrome = False
    End If

End Function
0
4/14/2009 4:29:29 PM
On 14 abr, 11:55, Luciano Paulino da Silva
<lucianopaulinosi...@gmail.com> 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 =A0 =A0 =A0 =A0 =A0 =A0 Number =A0 =A0 =A0 =A0 =A0Repeats =A0=
 =A0 =A0 =A0 Number
> QGAGGAAGGAGQ =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GA =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03
> GAG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 AG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A03
> GG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GAG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 2
> AA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 AA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A01
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A02
>
> Somebody could help me?
> Thanks in advance,
> Luciano

correction:

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
0
4/14/2009 4:31:10 PM
Thank you Noz!
I will try!
Luciano

On 14 abr, 13:29, Nozza <nozza_again_...@yahoo.co.uk> wrote:
> One of the first UDF's I wrote beack in 2005 was a palindrome checker.
> Could probably write it a little better now, but I wrote it to try to
> understand how to write a UDF
>
> Could be a start for you.
>
> Good luck
>
> Noz
>
> Function IsPalindrome(Selection) As Boolean
> 'Returns True if cell is a palindrome
>
> =A0 =A0 'Declarations
> =A0 =A0 Dim MyStringBefore As String
> =A0 =A0 Dim MyStringInProgress As String
> =A0 =A0 Dim MyStringReversed As String
> =A0 =A0 Dim CharacterToCheck As String
> =A0 =A0 Dim i As Integer
>
> =A0 =A0 'Check whether a range has been selected
> =A0 =A0 If TypeName(Selection) <> "Range" Then Exit Function
> =A0 =A0 MyStringBefore =3D UCase(Selection.Value)
>
> =A0 =A0 'strip out any non-letters
> =A0 =A0 For i =3D 1 To Len(MyStringBefore)
>
> =A0 =A0 =A0 =A0 CharacterToCheck =3D Mid(MyStringBefore, i, 1)
> =A0 =A0 =A0 =A0 If Asc(CharacterToCheck) >=3D 65 And Asc(CharacterToCheck=
) <=3D 90
> Then
> =A0 =A0 =A0 =A0 =A0 =A0 'found letter that is A-Z
> =A0 =A0 =A0 =A0 =A0 =A0 MyStringInProgress =3D MyStringInProgress & Chara=
cterToCheck
> =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 Next i
>
> =A0 =A0 'Mystringinprogress is now simply uppercase A-Z's with no spaces.
>
> =A0 =A0 For i =3D Len(MyStringInProgress) To 1 Step -1
> =A0 =A0 =A0 =A0 MyStringReversed =3D MyStringReversed & Mid(MyStringInPro=
gress,
> i, 1)
> =A0 =A0 Next i
>
> =A0 =A0 If MyStringReversed =3D MyStringInProgress Then
> =A0 =A0 =A0 =A0 IsPalindrome =3D True
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 IsPalindrome =3D False
> =A0 =A0 End If
>
> End Function

0
4/14/2009 5:16:07 PM
Reply:

Similar Artilces:

Solver? Maximum number of variables?
Is there a maximum number of adjustable variables that Solver can work with at any one time ... or do I have a memory limitation? Hi, It's 200, have a look here http://office.microsoft.com/en-us/excel/HP051992911033.aspx Mike "TK2301" wrote: > Is there a maximum number of adjustable variables that Solver can work with > at any one time ... or do I have a memory limitation? > > Mike H wrote: > Hi, > > It's 200, have a look here > > http://office.microsoft.com/en-us/excel/HP051992911033.aspx > > Mike >...

Creating a Macro to Compact all .pst files
Can someone point me to an example of a macro that would: Go through all open .pst files (I have about 20 pst files) Empty the trash in each .pst file Compact each .pst file I'm using Outlook XP on Windows XP Pro SP1. Outlook XP allegedly compacts automagically, though I have yet to observe it doing so. In any case, help on writing such a macro would be appreciated. Thanks in advance. Compacting a PST file is not something you can control from code. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder ...

Changing right side scrollbar bar row number box?
Hi, In excel when you click and hold the right hand scroll bar and move up and down the worksheet it displays the row number you are on. Is there any way to change that to pick up the value within column a instead? Many thanks, Rick I don't think so. In fact, when you upgrade to xl2k, those numbers disappear and the worksheet will scroll live--you'll see the worksheet change right before your eyes. (It works that way in xl2002. I _think_ xl2k is the first with this newer behavior.) "news.microsoft.com > > Hi, > > In excel when you click and hold the righ...

Number of categories
I need to make a macro to change the between tick-mark labels with a value depending of the total numbers of rows, but I do not now the VB code to put the number of categories in the graph. I'm not a expert in VB coding, any help is appreciated, Thanks, jos One of my favorite programming tools is the macro recorder. Go to Tools menu > Macros > Record New Macro, carry out the steps you want to automate, stop the recorder, then examine the recorded code to see what you need to insert into your procedure. You'll probably have to clean it up a bit. - Jon ------- Jon Peltier...

Copy and paste within a macro
I've created macro to copy the value of one field and I want it to paste this value into another field. However, it's pasting in the value that's stored in the macro. I tried deleting the TypeTo line after the cmdEditPaste line but now nothing gets pasted. How do I get it to paste in what gets copied each time (from the clipboard)? Elaine, Try doing an 'Edit Select All' first and then a 'Copy' and then an 'Edit Select All' and then 'Paste'. Remove the TypeTo after the paste. For instance, on the GL trx entry window if you wanted t...

Splitting Numbers in a Column #2
I certainly appreciate all the responses.... I have learned so much an thanks again... this site is so amazing.. -- jer10 ----------------------------------------------------------------------- jer101's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1076 View this thread: http://www.excelforum.com/showthread.php?threadid=26625 ...

How to make detail section expand/contract relative to number of l
My report contains various numbers of lines in the detail section, but the report is always the same length. How to make it shrink/expand as needed. ...

what to do when numbering style dropdown doesn't have what you nee
I've got an outline scheme where my first level is ARTICLE I - roman numeral for the 1st level - level two needs to be 1.1, 1.2, etc. - but when I click previous level number it inserts a roman numeral; I need it non-roman number in level 2; in general, I'm wondering how I can insert characters WHICH UPDATE in a numbering scheme, when they are not offered in the numbering style dropdown menu - let's say they offer .01, .02 - and I want .1, .2 - how do I get the control to create the characters I need to update - right now without using "previous level", I&#...

Eliminating leading spaces in numbers in a combo box
I have designed a database to record saving deposits made by members of a local Credit Union. A number of members act as collectors on a rota basis, when the office is open. The collection form has a combo box which is bound to the member's ID number in a "Member" table. The combo box is set to "Limited to List", and I use the "Not in List" event if a new member's ID Number is to be added. My problem is that several of the members seem to be pressing the space bar before they start typing the members ID. This triggers the "Not In List"...

Extracting some text from a string.
Hello, I'm trying to extract some specific text from a bunch of strings of text. I think this should be really easy but I can't get it done. I've used "Left" "Mid", "Substitute" and "Find" in one combination of another and sometimes it works but it doesn't always work. Regardless of the path, what I need is, the string of text after the final "\" and before the ".xls" I've copied a sample of the strings I'm working with below. C:\Source\qa\T P 2006\QT, IR, QTW, IRW\Tax Components\Calculation Testing...

Cannot find macro
I was alerted when I was opening an excel sheet that there were macros. The file was originally created by another person. However, when I went in and tried to delete them, I did not see any macro listed. Where can I find these macros? New users wrote: > I was alerted when I was opening an excel sheet that there were macros. The > file was originally created by another person. However, when I went in and > tried to delete them, I did not see any macro listed. Where can I find these > macros? See the messages with subject, macro warning. They solved this problem fo...

Read CD Serial Number
I am looking for a way to read the serial number off of a CD that is currently in a given drive. I'm assuming that there is a way to get this info, as it is used by CD-Player applications to determine which disc is currently inserted and to be able to retrieve the track listing from an associated database. I have also looked through the cdplayer.ini file and I see a specific number listed for each CD in the file. Any help you can give me on this would be wonderful. Thanks. - Robert Go to any search engine and type the following in the search box "read CD serial number&quo...

automation, macros ...
I'm ok with xcel macros and vba and would like to jump into outlook automation (fill in the greetings based on address, fill in signature ...) Where is the 101 stuff? Where's a good place to see examples? A good FAQ. Thank you. You might start at http://www.outlookcode.com/article.aspx?ID=40 -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "oldyork90" wrote: > I'm ok with xcel macros and vba and would like to jump into outlook > au...

how to alter the date within a macro
Hello, I was trying to create an archive function on a workbook. I have a formula throughout the workbook that is based on the current date using NOW(). I went in a recorded a macro of replacing the NOW() with last years date. How can I set this so that I can have the user push a button to archive the sheet and have the year based on the current date? I want to change the replacement to an argument that gives the year -1 at the time of clicking the archive button. The current recorded code follows. Thank You LWhite Cells.Replace What:="NOW()", Replacement:="Date (...

Stars instead of numbers
Hello Can anyone help me with the following: I need to display stars (*) in the cell instead of numbers, meaning that when I enter the number 5 in a cell I want to show 5 stars (*****) instead of the number 5. Thanks Maybe a custom cell format: "***";"***";"***" This will show any number as three stars, but text will be shown normally. Cheers, Joerg "John" <hpazar@hotmail.com> wrote in message news:9292B756-FC53-4B13-BCC0-CDE92A40AEDF@microsoft.com... > Hello > > Can anyone help me with the following: I need to display stars (*...

Search on Capital Letter
I am trying to figure out how to do a search on a capital letter in a document. What has happened is that I have lost the period at the end of a bunch of sentences. So i have the end of a sentence one space and the beginning of the next sentence that starts with the capital letter. This is in Word 2004 for Mac V11.5.6 using Mac 10.6.3 on a MacBook Pro. On 24/5/10 7:17 AM, in article 266b6bf1-877b-4df4-b69f-6c6d8cc1f11e@q33g2000vbt.googlegroups.com, "Tim" <twp002@gmail.com> wrote: > I am trying to figure out how to do a search on a capital letter in a > docume...

Incrementing Numbers
I am creating a Purchase Order template in Excel. I would like the Purchase Order number to be updated everytime I open it. I have seen posts in here for that function but I have no experience in typing in code or how to use it after typing the code. What I need is an answer for dummys :-) If someone could give me step by step instructions I would really appreciate it. Thank you. Liz Valorz Elizabeth I recommend that you increment the number (automatically) when you print the Purchase Order rather than when you open the file. That would preclude the often occurring incidence of n...

Check boxes need to return a number
Hi, I have not done anything with check boxes before, so I am reall new at this. I am trying to do a sheet that will have check boxes tha when checked will return a number from another sheet. When the box i not checked, it should return a "0". If I just use a check column, don't have any problems with the formula to pull the number from th other sheet when you put an "x" in the column but I would like to us check boxes or buttons. I will be sharing this will peers and woul like to just make it a simple click. I have attached a copy of what have completed. Only...

creating data for values between 2 numbers
This is something that I have been trying to do for a long time and sort of gave up on it... but since the issue came up again, I’m going to try to ask for help again. I issue out forms, all with sequential numbers on them, to customers. These forms have lots of data on them like Name, License plate number, destination, ext. Right now with my current database, if I issue 1000 forms to the same person, and they are using the same vehicle, I have put all the data in the 1st form, save it, carry it over, and then change the number and save it again... and do this 1000 times unt...

Outlook Macro using SendKeys in OL2007...
Hi, We have a macro that worked great until installed with OL2007. It sends some keys to a custom form to check a few checkboxes, etc. We did add it to the toolbar so we could click it, but it doesn't send the keys any longer. Any ideas? Thanks, Alan http://www.sadevelopment.com Partition Boot Manager and Large Drive Tools utilities! SendKeys is never a good programming solution. In this case, the = keystrokes are probably different. But you really you should be setting = property values using the Outlook object model instead.=20 FYI, there is a newsgroup specifically for gene...

Letter Writing Assistant and Collections Management work together
Would like the Letter Writing Assistant feature to work directly with the Collections Management module as the Letter Writing Assistance provides letters through Microsoft Word which allows more features/capabilities than the text reports through Collections Management and Report Writer. We have been told that if you have the collection module installed, then letter writing will not work. Is this true? "Distribution support" wrote: > Would like the Letter Writing Assistant feature to work directly with the > Collections Management module as the Letter Writing Assistanc...

Trouble with CommandText string concatenation
Hi everyone, I'm trying to assign a command to an OleDbCommand object and concatenate a string parameter without much success. Here's a basic idea of what I'm trying to do for my school project against an Access db. string sCmd; OleDbCommand * cmd = new OleDbCommand(); sCmd = "INSERT INTO Books (Title) VALUES (' " + dbFile.Title; //dbFile.Title is also a string cmd->CommandText = sCmd; I then get the following error: error C2664: 'System::Data::OleDb::OleDbCommand::set_CommandText' : cannot convert parameter 1 from 'std::string' to 'System...

Excel: Can't save changes to print number of copies setting
I inherited an Excel workbook that has the number of copies to print set to 10. I change it to 1, then save the file, but it always reverts back to ten. ...

wildcard in filenames in macro
Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. OK, OK!! Should be 2007! "Wes_A" wrote: > E...

How to -> Start Excel from command line and run a macro
I have an Excel spreadsheet with a macro. What I want to do is to be able to start this spreadsheet via a BAT file(mainly to schedule the running). But the kicker is to run the Macro. I realize I may have to disable the security to get passed the ENABLE/DISABLE prompt..or is there another way to handle that too? I have gotten close by getting EXCEL to load my spreadsheet but I am stuck on the "right way" to handle the prompt and autostart the macro. Any help will be appreciated. -- Mickey A Assuming that you have Microsoft Excel 2003, this might help: Assistance > E...