Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Find the String is Palindrome or Not


Reply
 
Thread Tools Display Modes
  #1  
Old 02-06-2008, 07:04 AM
ethiraj ethiraj is offline
Freshman
 
Join Date: Jan 2008
Posts: 44
Default Find the String is Palindrome or Not


Hi anyone

How to find the given String is palindrome or Not..using Excel Formulas or Excel Functions....


Kindly Reply...

Thanks in Advance
Reply With Quote
  #2  
Old 02-06-2008, 07:17 AM
the master's Avatar
the master the master is offline
Tachikoma
 
Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,595
Default

Ive looked through all the text functions and i cant find anything that would do this for you. The easiest way is to reverse the string and check it against the origional but excel wont let you reverse a string.

It would be possible to do this using VBA
__________________
"That which seems simple is often overlooked" ~ me
Halloween Special FX
Web Hosting from Spook Technologies
Reply With Quote
  #3  
Old 02-06-2008, 07:51 AM
mkaras's Avatar
mkaras mkaras is offline
Ultimate Contributor

Retired Leader
* Expert *
 
Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874
Default

Some palindromes involve spaces in the string between words and these are oftten stripped out before the process of reversing the letters for comparison. If this is the case then this case would need to be added to the VBA code.

There is another similar word play where expressions are reversed word order and not reversed letter order. In these the reverse and comparison operation is more complex than a simple letter reversal. Another job better left to VBA instead of Excel formulas.
Reply With Quote
  #4  
Old 02-06-2008, 07:54 AM
the master's Avatar
the master the master is offline
Tachikoma
 
Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,595
Default

Excel is capable of stripping out spaces and other things like "-" by itself. Its just a shame it doesnt do the 1 bit you really need
__________________
"That which seems simple is often overlooked" ~ me
Halloween Special FX
Web Hosting from Spook Technologies
Reply With Quote
  #5  
Old 02-06-2008, 10:43 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

Well... this one looked like a bit of a challenge.... so I did a google and the consensus is that this cannot be done with an excel formula.

So I'm feeling a little bit chuffed with myself that I've managed to do it!
If anyone can find a solution anywhere else then please let me know.

This array formula will return true or false for a word up to 20 characters in length in the cell A1. It is not case sensitive and checks the symmetry of all characters in the cell.

Code:
=IF(SUM(--((MID($A$1,1+POSITION,1))=(IF(ISERROR(MID($A$1,LEN($A$1)-POSITION,1)),0,MID($A$1,LEN($A$1)-POSITION,1)))))=LEN($A$1),TRUE,FALSE) Where POSITION is an array constant defined as: ={0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}

Increasing the size of the array constant will increase the number of characters it will solve for.

If anyone wants a breakdown on how it works then let me know.

Last edited by Colin Legg; 02-07-2008 at 03:54 AM. Reason: It is NOT case sensitive
Reply With Quote
  #6  
Old 02-06-2008, 06:10 PM
the master's Avatar
the master the master is offline
Tachikoma
 
Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,595
Default

Ive searched google too and its tru that this cant be done in excel so your formula really is something special. Sure it has its limitations (The 20 char limit) but its still really impressive
__________________
"That which seems simple is often overlooked" ~ me
Halloween Special FX
Web Hosting from Spook Technologies
Reply With Quote
  #7  
Old 02-06-2008, 06:55 PM
mkaras's Avatar
mkaras mkaras is offline
Ultimate Contributor

Retired Leader
* Expert *
 
Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874
Default

Chris:
I could not get your formula to return TRUE for any other A1 cell content than when the cell is either empty or contains a single character.

Maybe I do not fully understand how to define the array constant. I plunked the expression with the 0--19 sequence in it into a certain cell and then applied a named range value POSITION to that cell.
Reply With Quote
  #8  
Old 02-06-2008, 09:09 PM
ethiraj ethiraj is offline
Freshman
 
Join Date: Jan 2008
Posts: 44
Default

thanks colin..


I ensure that your solution is the only way to get the Palindrome and its Works fine....
Reply With Quote
  #9  
Old 02-06-2008, 09:15 PM
ethiraj ethiraj is offline
Freshman
 
Join Date: Jan 2008
Posts: 44
Default

Go to Insert Menu and Select Define Name..Here you add whatever the constants you wish. give the Constant name in Top most box and Give the Array Values in Refers to Area then press Add Button...
Reply With Quote
  #10  
Old 02-06-2008, 11:40 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

You're welcome ethiraj!


Quote:
mkaras Chris:
I could not get your formula to return TRUE for any other A1 cell content than when the cell is either empty or contains a single character.

Maybe I do not fully understand how to define the array constant. I plunked the expression with the 0--19 sequence in it into a certain cell and then applied a named range value POSITION to that cell.
Hi Michael,

You can read up on array constants and how to define them HERE.

Remember, this is an array formula so when you type it into the formulabar you must finish your entry with CTRL+SHIFT+ENTER and Excel will automatically surround it with {}.

I hope you can get it to work now. I'll edit this post later to attach a working spreadsheet in case anyone has problems.


Hi The Master,

Yes, it's limited to 20 characters in the example I gave - but if you increase the elements in the array constant then it will solve for more characters.
Regards
Colin
Attached Files
File Type: zip Palindrome Array Formula.zip (4.7 KB, 11 views)

Last edited by Colin Legg; 02-07-2008 at 01:42 AM. Reason: Added workbook
Reply With Quote
  #11  
Old 02-07-2008, 03:20 AM
mkaras's Avatar
mkaras mkaras is offline
Ultimate Contributor

Retired Leader
* Expert *
 
Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874
Default

Colin:

Really sorry for trying to rename you to Chris the last time I posted.

I learned a new concept naming a constant in Excel without sucking up a cell to do so. This could be used to create spreadsheets that the average user (like me) would be kind clueless to figure out.

I downloaded the spreadsheet that you zipped above and find that it displays TRUE all the time irregardless of what is in cell A1.

I'll try starting again from scratch and see if I can get it to work that way.
Reply With Quote
  #12  
Old 02-07-2008, 03:32 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

Quote:
Colin:

I downloaded the spreadsheet that you zipped above and find that it displays TRUE all the time irregardless of what is in cell A1.

I'll try starting again from scratch and see if I can get it to work that way.
Michael:

I don't know why that is happening.
I downloaded the spreadsheet from the thread and it works fine for me: TRUE if palindrome; FALSE if not.

Please note typo on my previous thread (I have gone back and edited it so it does not confuse anyone else) - this solution is NOT case sensitive. Maybe that was misleading you?

I will post case sensitive solution in a minute....

Last edited by Colin Legg; 02-07-2008 at 03:55 AM. Reason: Typo amendment....
Reply With Quote
  #13  
Old 02-07-2008, 03:48 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

If you want a case senstive solution then you use this array formula instead:

Code:
=IF(SUM(--(IF(ISERROR(CODE(MID($A$1,1+POSITION,1))),"a",CODE(MID($A$1,1+POSITION,1)))=IF(ISERROR(CODE(MID($A$1,LEN($A$1)-POSITION,1))),"b",CODE(MID($A$1,LEN($A$1)-POSITION,1)))))=LEN($A$1),TRUE,FALSE)

Regards
Colin
Reply With Quote
  #14  
Old 02-07-2008, 08:46 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,876
Default

Quote:
Originally Posted by mkaras View Post
I downloaded the spreadsheet that you zipped above and find that it displays TRUE all the time irregardless of what is in cell A1.

I'll try starting again from scratch and see if I can get it to work that way.
Automatic calculation was turned off, so enable it in 'Tools/Options.../Calculation' or press F9 manually.

You'll notice his formula is enclosed in {}. This means it is an array formula, so you must use 'Ctrl-Shift-Enter' instead of just 'Enter'.
The {} will be added automatically and the interal iterator will function using his defined array constant.
__________________
My Intel Paxville CPUs (dual socket) are supported by Windows 8.0 (which patch support ends for on Jan. 12 2016), but are not supported by Windows 8.1 due to a lack of a PrefetchW instruction. This instruction could be patched out with a NOP for borderline CPUs, if Microsoft chose to. So, Windows 7 it is.
Reply With Quote
  #15  
Old 02-07-2008, 04:09 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,876
Default

I was trying for a simpler array formula using the same POSITION array, but can't get it to work quite right, so maybe it won't work at all. I'll show it anyway:
{=EXACT(MID(A1,POSITION+1,1),MID(A1,LEN(A1)-POSITION,1))}
__________________
My Intel Paxville CPUs (dual socket) are supported by Windows 8.0 (which patch support ends for on Jan. 12 2016), but are not supported by Windows 8.1 due to a lack of a PrefetchW instruction. This instruction could be patched out with a NOP for borderline CPUs, if Microsoft chose to. So, Windows 7 it is.
Reply With Quote
  #16  
Old 02-08-2008, 04:08 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

Hi Cerian Knight

Using the EXACT function is a great idea!

Your current formula doesn't "work" due to the fact that it is a multiple result array formula and therefore the "real" result cannot be viewed in one cell.

I've used your idea to build a slightly alternative version which will work:

Code:
=COUNT(IF(EXACT(MID(A1,POSITION+1,1),MID(A1,LEN(A1)-POSITION,1))=FALSE,1))=0


And that's definitely an improvement on the solution in #13! Perhaps it can be improved even further?

Last edited by Colin Legg; 02-08-2008 at 05:06 AM.
Reply With Quote
  #17  
Old 02-08-2008, 07:53 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,876
Default

Cool, you got it to work! It occurred to me after I posted that EXACT is really just an '=' in disguise. So how about this for further improvement:
Code:
=COUNT(IF(MID(A1,POSITION+1,1)<>MID(A1,LEN(A1)-POSITION,1),1))=0
At this point, shortening 'POSITION' to just 'P' would yield a significant reduction.
__________________
My Intel Paxville CPUs (dual socket) are supported by Windows 8.0 (which patch support ends for on Jan. 12 2016), but are not supported by Windows 8.1 due to a lack of a PrefetchW instruction. This instruction could be patched out with a NOP for borderline CPUs, if Microsoft chose to. So, Windows 7 it is.
Reply With Quote
  #18  
Old 02-08-2008, 08:09 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

It's a good try... but EXACT is not entirely the same as '=' .... so that one won't be case sensitive.....
....so let's call it an improvement on #5!

So now we have 2 best solutions to solve up to 20 characters -
Code:
Case Sensitive: =COUNT(IF(EXACT(MID(A1,POSITION+1,1),MID(A1,LEN(A1)-POSITION,1))=FALSE,1))=0 Not Case Sensitive: =COUNT(IF(MID(A1,POSITION+1,1)<>MID(A1,LEN(A1)-POSITION,1),1))=0 where POSITION is a horizontal or vertical array constant with values 0 to 19.

And, as you pointed out, we can also shorten the name of the array constant too.


Any more offerings!?

(Because this problem has never been solved before and I can't find any documentation on using an array constant to parse text into an array, I think I'm going to put this in as a step by step example in the Excel Array Formulae Tutorial which is currently being reviewed in the Tech Discussions Board - so any other improvements/suggestions would be fantastic!)
Reply With Quote
  #19  
Old 02-08-2008, 09:50 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,876
Default

Quote:
Originally Posted by Colin_L View Post
It's a good try... but EXACT is not entirely the same as '=' ....so that one won't be case sensitive.....
I hadn't noticed that before...good to know. Looks like I came up with EXACT despite myself.
Quote:
So now we have 2 best solutions to solve up to 20 characters -
Since we are searching 'front to back' and 'back to front' and the middle character in an odd length is irrelevant...I believe we can do 41 characters without changing the definition of POSITION!
__________________
My Intel Paxville CPUs (dual socket) are supported by Windows 8.0 (which patch support ends for on Jan. 12 2016), but are not supported by Windows 8.1 due to a lack of a PrefetchW instruction. This instruction could be patched out with a NOP for borderline CPUs, if Microsoft chose to. So, Windows 7 it is.
Reply With Quote
  #20  
Old 02-08-2008, 10:11 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,876
Default

Alright, forget all that...suppose we just get rid of the named array constant altogether:
Code:
'This array formula will handle a BIG string:
=COUNT(IF(EXACT(MID(A1,ROW(A:A),1),MID(A1,LEN(A1)-ROW(A:A)+1,1)),"",1))=0
__________________
My Intel Paxville CPUs (dual socket) are supported by Windows 8.0 (which patch support ends for on Jan. 12 2016), but are not supported by Windows 8.1 due to a lack of a PrefetchW instruction. This instruction could be patched out with a NOP for borderline CPUs, if Microsoft chose to. So, Windows 7 it is.

Last edited by Cerian Knight; 02-08-2008 at 10:38 AM. Reason: Simplified ROW expression and comparison
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->