Excel and palindromes (continuation)

by admin No comments »

Palindrome list

In my last post about Excel and palindromes I showed you how to check if a sentence is a palindrome. Today, based on the list of different sentences (including palindromes) we will prepare an automatically generated list containing only palindromes.

Excel and list of palindromes

We will use a helper column (column F in my case). Let’s select the amount of cells in our helper column to equal the number of sentences in our source list. In my case it’s 7 cells.

Excel and palindromes
Next, let’s type in the following array formula:

{=SMALL(IF($D$8:$D$14,ROW($D$8:$D$14),""),ROW(INDIRECT("1:"&COUNT(IF($D$8:$D$14,ROW($D$8:$D$14),"")))))}

The formula uses the “is palindrome?” column ($D$8:$D$14) we talked about last time. For each found palindrome it returns the palindrome’s row number, and the error value #N/A otherwise.

What’s left now is to type the following formula into the first cell of my palindrome’s list:

=IF(ISERROR(INDIRECT(ADDRESS(F8,2))),"",INDIRECT(ADDRESS(F8,2)))

We could alternatively use a simpler formula:

=IF(ISERROR(F8),"",INDEX($B:$B,F8))

and copy it down to the cells below. Should work.

Marcin

Excel and palindromes

by admin No comments »

A palindrome is a word (or a sentence) which can be read backwards and doesn’t loose it’s meaning. Actually, the sentence means exactly the same while read either way. A few examples:

  • So many dynamos!
  • Rise to vote, sir
  • No devil lived on
  • Do geese see God
  • Are we not drawn onward, we few, drawn onward to new era?

Let’s prepare a formula which will return TRUE when the selected string is a palindrome. In my next blog entry, based on the column with many text strings (including palindromes) we will also generate a list containing ONLY palindromes.

Our worksheet, before we type in any formulas, looks like this:

Excel and palindromes

Our list of palindromes and cells waiting to be filled up with Excel formulas

To check if a string is a palindrome I will loop through all the string’s letters (form left to right) and compare, in sequence, each letter with it’s “mirrored” equivalent. The first and the last letter have to be the same, the second and the second last has to be the same and so on.

If this condition is fulfilled it means that the string is a palindrome.

My first approach to the problem below (it’s an array formula what means that you enter it using SHITS+CTR+ENTER)

{=SUM((UPPER(MID(SUBSTITUTE(B8," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B8," ","")))),1))=UPPER(MID(SUBSTITUTE(B8," ",""),ABS(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B8," ",""))))-(LEN(SUBSTITUTE(B8," ",""))+1)),1)))*1)=LEN(SUBSTITUTE(B8," ",""))}

The cell B8 contains the string to be analyzed.

How does it work?

As I said earlier, the formula compares each letter from the beginning of the string with the symmetrically located letter from the end. If two letters are the same the formula returns TRUE for each comparison.

Each logic value is then multiplied by 1 (to get a number which could be used in mathematical calculations) .

Finally the formula adds up all the numbers. If the sum is equal to the length of the string it means that we have just discovered a palindrome. I hope that the picture below will help you a little in understanding what I am talking about.

Excel and palindromes
To make the formula uninfluenced by the size of the letter (upper/lowercase) I used function UPPER to convert all letters into uppercase before I start comparing them. I have also removed all spaces from the string using function SUBSTITUTE.

Imperfections

1.

First of all the formula doesn’t seem to work properly with strings containing periods and commas. You can see it on the picture below.

Excel and palindromes

Formula doesn't work properly with sentences containing periods and commas.

In all cases with single words or sentences without any punctuation the formula returns correct results. But in other cases (I marked it red) the output is wrong. The problem can be solved by removing from all strings not only spaces, but also commas and periods.

To do so, we will replace function

SUBSTITUTE(B8," ","")

used in the above formula with

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8," ",""),",",""),".","")

The problem with this approach is that there are too many nested formulas and Excel (at least in version 2003) will return an error. We could think about a better algorithm, but since we already started with the method described we will trick Excel a little. Let’s create a defined name “ourstring” which will refer to the formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8," ",""),",",""),".","")

Now we will use this defined name in our formulas and Excel shouldn’t say “NO”. The results returned are now correct.

Excel and palindromes

Improved formula returns correct results.

2.

Right now, our formula slides through all letters (comparing them), from left to right and, at the same time, from right to left. As a result, the sentence characters are compared twice. When the formula starts it compares the first character with the last, and later after it reaches the end the last character is being compared again with the first one. It’s unnecessary.

So, let’s tweak our formula so it doesn’t do this extra work anymore:

={SUM((UPPER(MID(ourstring,ROW(INDIRECT("1:"&ROUND(LEN(ourstring)/2,0))),1))=UPPER(MID(ourstring,ABS(ROW(INDIRECT("1:"&ROUND(LEN(ourstring)/2,0)))-(LEN(ourstring)+1)),1)))*1)=ROUND(LEN(ourstring)/2,0)}

Have fun with palindromes!

Marcin