Posts Tagged ‘function INDIRECT()’

Excel and palindromes (continuation)

January 10th, 2010

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:


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:


We could alternatively use a simpler formula:


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