

02062008, 07:04 AM

Freshman


Join Date: Jan 2008
Posts: 44


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


02062008, 07:17 AM


Tachikoma


Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,594


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


02062008, 07:51 AM


Ultimate Contributor
Retired Leader * Expert *


Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874


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.


02062008, 07:54 AM


Tachikoma


Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,594


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


02062008, 10:43 AM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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; 02072008 at 03:54 AM.
Reason: It is NOT case sensitive

02062008, 06:10 PM


Tachikoma


Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,594


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


02062008, 06:55 PM


Ultimate Contributor
Retired Leader * Expert *


Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874


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 019 sequence in it into a certain cell and then applied a named range value POSITION to that cell.


02062008, 09:09 PM

Freshman


Join Date: Jan 2008
Posts: 44


thanks colin..
I ensure that your solution is the only way to get the Palindrome and its Works fine....


02062008, 09:15 PM

Freshman


Join Date: Jan 2008
Posts: 44


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...


02062008, 11:40 PM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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 019 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

Last edited by Colin Legg; 02072008 at 01:42 AM.
Reason: Added workbook

02072008, 03:20 AM


Ultimate Contributor
Retired Leader * Expert *


Join Date: Mar 2004
Location: Beaverton, OR
Posts: 1,874


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.


02072008, 03:32 AM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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; 02072008 at 03:55 AM.
Reason: Typo amendment....

02072008, 03:48 AM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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


02072008, 08:46 AM


MultiTechnologist
Super Moderator * Expert *


Join Date: May 2004
Location: Michigan
Posts: 3,813


Quote:
Originally Posted by mkaras
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 'CtrlShiftEnter' instead of just 'Enter'.
The {} will be added automatically and the interal iterator will function using his defined array constant.

__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to twodimensional semicircular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed redefinition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.

02072008, 04:09 PM


MultiTechnologist
Super Moderator * Expert *


Join Date: May 2004
Location: Michigan
Posts: 3,813


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))}

__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to twodimensional semicircular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed redefinition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.

02082008, 04:08 AM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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; 02082008 at 05:06 AM.

02082008, 07:53 AM


MultiTechnologist
Super Moderator * Expert *


Join Date: May 2004
Location: Michigan
Posts: 3,813


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.

__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to twodimensional semicircular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed redefinition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.

02082008, 08:09 AM


Out Of Office
Retired Moderator * Expert *


Join Date: Mar 2005
Location: London, UK
Posts: 3,398


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!)


02082008, 09:50 AM


MultiTechnologist
Super Moderator * Expert *


Join Date: May 2004
Location: Michigan
Posts: 3,813


Quote:
Originally Posted by Colin_L
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!

__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to twodimensional semicircular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed redefinition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.

02082008, 10:11 AM


MultiTechnologist
Super Moderator * Expert *


Join Date: May 2004
Location: Michigan
Posts: 3,813


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

__________________
Instead of 2*Pi, Tau should be defined as Pi/2 to respect least utilitarian granularity of the constant. In basics, the single to twodimensional semicircular transformation distance required to travel between two points demands this (Pi/2:1). The reader is free to demonstrate the numerous further examples of the utility of this proposed redefinition (including e^(pi/2*i) = i). Tau = Pi/2 was originally proposed by Albert Eagle in 1958, so the more recent proposal as 2*Pi is unfortunate.
Last edited by Cerian Knight; 02082008 at 10:38 AM.
Reason: Simplified ROW expression and comparison

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


Thread Tools 

Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off





