0
=IF(MID(B3,ROW(A:A),1)=MID(B3,LEN(B3)+1-ROW(A:A),1),"T")

I was reading Palindrome in cell B3 with above formula, it works fine(if find without punctuation & spaces). but when I try to read Palindrome with punctuation and spaces it did not read well.

for example:-

B3:lol---->Palindrome B3:l ol---->Palindrome ........( but as space occurs between l & ol , it should not be Palindrome)

I am not understanding what points I missed in my formula? can anyone please help me get my formula universal ? Thanks!


Regards,

0

Your current formula has several large issues, so I'm very surprised to hear you declare that it "works fine".

First of all, there is no external function acting on the IF statement. Hence, all but the very first element in the array produced by the comparison is considered. In effect, your formula will return "T" if the first letter in your string matches the last: all other letters within the string are completely redundant as far as this expression is concerned. According to your formula, a string such as ABCDEA is considered to be a palindrome. And this is also why your example of "L OL" returns "T" - the middle characters simply do not matter.

Secondly, you are array-entering a formula with an entire column's worth of entries being passed to the ROW function to generate the array to pass as MID's start_num parameter. This not only means that Excel is having to generate an array consisting of over one million elements (requiring an astonishing and unnecessary amount of resource), but it also means that your second array will consist of thousands and thousands of negative values, which of course will result in errors when passed to MID.

Try:

=IF(SUMPRODUCT(N(MID(B3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1)<>MID(B3,LEN(B3)+1-ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1)))=0,"T","")

Regards

| improve this answer | |
  • Thank you XOR! to let me know about IF more clearly & for this code :) – Hemant Rupani Mar 13 '15 at 18:44
  • You're very welcome. – XOR LX Mar 13 '15 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.