 # Formula Forensics 006. Palindromes

Chandoo wrote a post in August 2011 where he looked at determining if a cell contained a palindrome.

Chandoo presented a formula for determining if a cell; C1; contains a palindrome:

`=IF(SUMPRODUCT((MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)=MID(C1,LEN(C1)-ROW( OFFSET(\$A\$1,,, LEN(C1) )) +1, 1))+0)=LEN(C1),"It’s a Palindrome","Nah!")`

And then Chandoo challenged everyone:

How does this formula work?

Well, that is your weekend homework.

So today we’re going to complete our homework and pull apart the above formula and see what makes it tick.

## Palindrome Setup

In a blank worksheet enter

C1: “Chandoo” without the brackets

D1: `=IF(SUMPRODUCT((MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)=MID(C1,LEN(C1)-ROW( OFFSET(\$A\$1 ,,, LEN(C1)))+1,1))+0)=LEN(C1),"It’s a Palindrome","Nah!")`

In the example below we will work on two words:

Firstly, “Chandoo” which is clearly not a Palindrome

and Secondly, “Radar” which is a Palindrome

## Non-Palindrome Analysis

The main structure of this formula is that it is a simple If () function.

The Excel If() function is defined by 3 parts

=If(Condition, Value if True, Value if False)

Our Formula is

`=IF(`` SUMPRODUCT(( MID(C1, ROW( OFFSET( \$A\$1,,,LEN(C1))), 1) = MID(C1, LEN(C1) - ROW( OFFSET( \$A\$1,,,LEN(C1))) + 1, 1)) + 0) = LEN(C1)``,`` "It’s a Palindrome"``, ``"Nah!"``)`

Condition:                 `SUMPRODUCT((MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1) = MID(C1, LEN(C1) - ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1))+0)=LEN(C1)`

`Value if True: ``"It’s a Palindrome"`

`Value if False: ``"Nah!”`

Lets not waste time on the two Values if True/False as they are purely a message to the user, the real work happens in the Condition part of the If() function.

The Condition does all the work: `SUMPRODUCT((MID(C1,ROW( OFFSET(\$A\$1,,, LEN(C1))),1) = MID(C1, LEN(C1)-ROW( OFFSET(\$A\$1,,,LEN(C1)))+1,1))+0)``=``LEN(C1)`

`Is a Sumproduct and a Len`

```That is the formula is doing a calculation of the sumproduct of some inner calculations and comparing the answer to the length of the contents of cell: C1 in the example of "Chandoo" = Len(C1) = 7 ```

` `

`SUMPRODUCT((`` ``MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)``=`` ``MID(C1, LEN(C1)-ROW( OFFSET(\$A\$1,,, LEN(C1)))+1,1)``)+0)``=LEN(C1)`

Lets now look at the two inner calculations:

`MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)`

Copy this calculation into E7

=` MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1) ``Don’t press Enter, press F9 `

```(If using the example file goto cell E9, Press F2 and then F9) ```

`Excel will return {"C";"h";"a";"n";"d";"o";"o"} Don’t press Enter, press Esc when ready`

`It is an Array of the letters in the cell C1`

Now do the same for the second part of the equation:

Copy this calculation into E8

=` MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1) ``Don’t press Enter, press F9`

`Excel will return `{“o”;”o”;”d”;”n”;”a”;”h”;”C”}

You will notice that this array is the reverse of the word in C1

We will come back to how these two equations work in a minute or two

But note that we now have

Sumproduct((`{"C";"h";"a";"n";"d";"o";"o"}=`{“o”;”o”;”d”;”n”;”a”;”h”;”C”})+0)

We can evaluate the inner part of this to see what happens

Copy this calculation into E10

`={"C";"h";"a";"n";"d";"o";"o"}=`{“o”;”o”;”d”;”n”;”a”;”h”;”C”} `Don’t press Enter, press F9`

`Excel will return an Array `{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

This means that only the 4th letter or “n” in Chandoo is the same forward and backwards.

Looking at the next bit

Copy this calculation into E12

=(`{"C";"h";"a";"n";"d";"o";"o"}=`{“o”;”o”;”d”;”n”;”a”;”h”;”C”})+0 `Don’t press Enter, press F9`

`Excel will return `{0;0;0;1;0;0;0}

Excel has converted the false/True array above into an array of 0’s and 1’s

Finally in E14 evaluate:

=Sumproduct({0;0;0;1;0;0;0})

Is evaluated and Excel adds up all the numbers returning a 1 as the answer.

So the original equation :

`=IF(``SUMPRODUCT((MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)=MID(C1,LEN(C1)- ROW( OFFSET(\$A\$1,,, LEN(C1))) +1,1)) +0 )=LEN(C1)``,``"It’s a Palindrome"``,``"Nah!"``)`

Is simplified as

`=IF( ``1 = LEN(C1)``,``"It’s a Palindrome"``,``"Nah!"``)`

Now C1 has the Word “Chandoo “ in it which is 7 letters long

`=IF( ``1 = 7``,``"It’s a Palindrome"``,``"Nah!"``)`

So the If() function returns the False answer of “Nah!”

## Non-Palindrome Analysis

If we place a Palindrome such as “Radar” in C1 and skip backwards to the

`SUMPRODUCT((`` ``MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)``=`` ``MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)``)+0)``=LEN(C1)`

Section , Evaluating each part again we see

`E21: MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)`

`Evaluates to {"R";"a";"d";"a";"r"}`

`And`

`MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)`

`Evaluates to: {"r";"a";"d";"a";"R"}`

`And`

`(`` ``MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)``=`` ``MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)``)`

`Evaluates to: {TRUE;TRUE;TRUE;TRUE;TRUE}`

With

`SUMPRODUCT((`` ``MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)``=`` ``MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)``)+0) `

`Evaluating to: 5`

`Which is clearly equal to the length of the word “Radar” and so the If() function returns “``It’s a Palindrome”`

## A Minute Later

` `

`But how do the middle bits`

`MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)`

`and`

`MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)`

`Work?`

`The two equations are effectively the same`

`The first works left to right and extracts each letter one at a time`

`The second works right to left and extracts each letter one at a time`

`How Does`

`=MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1) F9`

`Evaluate to {"C";"h";"a";"n";"d";"o";"o"}`

`=MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1) F9`

Is a simple Mid() function which takes the `1 `Character at position `ROW(OFFSET(\$A\$1,,,LEN(C1))) from the contents of C1`

`What is ``ROW(OFFSET(\$A\$1,,,LEN(C1)))`

`Is used to return an array of numbers from 1 to Len(C1) in this case 7`

```eg: {1;2;3;4;5;6;7} ```

`So in E16 enter =ROW(OFFSET(\$A\$1,,,LEN(C1))) and press F9`

`Excel evaluates it to {1;2;3;4;5;6;7}`

So the function `=MID(C1,ROW(OFFSET(\$A\$1,,,LEN(C1))),1)`

`Returns the 1st, 2nd. 3rd, 4th, 5th, 6th & 7th characters from C1 as an Array`

## How Does `=ROW(OFFSET(\$A\$1,,,LEN(C1))) work?`

`=ROW(OFFSET(\$A\$1,,,LEN(C1)))`

Takes the Row of the range defined by the Offset Function

Note that `OFFSET(\$A\$1,,,LEN(C1)) is a simple Offset that sets up a range`

`The excel Offset Function is defined as `

`=Offset(Reference, Rows, Columns, [Height], [Width])`

In our example

`=OFFSET(\$A\$1,,,LEN(C1))`

Will return a Range which is referenced to A1, has no Row or Column offset and is the length of cell the contents of Cell C1

Effectively returning a range A1:A7

Because this is all in an Sumproduct formula, Excel evaluates this formula for each value in the Range

And so

`=ROW(OFFSET(\$A\$1,,,LEN(C1))) `

`evaluates it to `

`{1;2;3;4;5;6;7}`

Which is then used extract the characters from the word in C1 into an Array as `{"C";"h";"a";"n";"d";"o";"o"}`

` `

`A similar process applies to the second half of the two equations`

`MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1)`

Except that it is evaluated from the Right to Left of the Word in C1 by use of the

`LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1`

`In the Mid Equation`

## Summary

So in summary we use Sumproduct to compare two Arrays, which contain the word and the word reversed, to each other. The Sumproduct counts the number of common matches and then this is compared to the length of the word.

If the two match the word is a Palindrome.

## OTHER POSTS IN THIS SERIES

You can learn more about how to pull Excel Formulas apart in the following posts which are all included in the Formula Forensic Series:

I am running out of ideas for Formula Forensics and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003 or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui. Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Hui... Tags: if(), len(), mid(), OFFSET(), Pailindrome, row(), sumproduct Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 13 Responses to “Formula Forensics 006. Palindromes”

1. Jonas says:

Nice post! I wonder if there is a way to utilise the = MID(C1, LEN(C1)-ROW(OFFSET(\$A\$1,,,LEN(C1)))+1,1) formula to reverse a word? In other words, a formula that would give the result "oodnahC" if the input is "Chandoo".

• Hui... says:

@Jonas
Unfortunately there isn't any way to append the array elements together as you have correctly identified
If Microsoft had properly implemented Ranges as an input to the Concatenate function, as they rightfully should have, that should have been possible

2. Luke M says:

Excellent write-up Hui. Thanks for showing how to break up a word into the individual characters.

@Jonas
As Hui said, you can't do it with native formulas. Here's a short UDF I use when I need to reverse a word.
Function Reverse(r As String) As String

For i = Len(r) To 1 Step -1
Reverse = Reverse & Mid(r, i, 1)
Next i

End Function

3. Jonas says:

Thanks for the UDF! Better than nothing 🙂 I have to agree with you Hui that it's a bit strange that Microsoft didn't implement it.

4. simchuck says:

@Jonas:
I was just looking for this yesterday and found a nice concise UDF to concatenate a range...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=817

I agree that it should be part of the built-in function, but as long as you don't mind a little bit of VBA this will do the job nicely.

5. Hui... says:

@Jonas, Simchuck
there are several variations in the Comments as well

6. K Macdonald says:

The formulas are pretty awesome but I am also inclined to want to create a UDF and then compare the original to the reversed for a match. Admitted I can lift the concept from an already existing Library and tailor it to VBA in a fraction of the time it takes to understand a formula which runs out to 100 characters. I take the point that not every body is au fait with a programming language and you do introduce security concerns with macros

7. Chandrashekhar Joshi says:

Dear Chandoo & Hui,

Nice one and very informative. as luke mentioned I always use VBA & helper column to check whether given string is pelindrom of not. here the code
Function pd(aa As String)
aa = Trim(aa)
aa = UCase(aa)
Dim tmp As String
Dim a As Integer
a = Len(aa)
For i = Len(aa) To 1 Step -1
tmp = tmp & Mid(aa, a, 1)
a = a - 1
Next i
pd = tmp
End Function
can u please explain if I want to use sub how can I return true or false?
THanks everyone

8. Hui... says:

Chandrashekhar
Try this function:
```Function pd(aa As String) pd = False If StrReverse(UCase(Trim(aa))) = UCase(Trim(aa)) Then pd = True End Function```

9. Hui... says:

Chandrashekhar
Also I note that your original function only returns the Upper Case Trimmed reference
=pd("Chandoo") = OODNAHC
.
It can be simplified as
.
```Function pd(aa As String) pd = UCase(Trim(StrReverse(aa))) End Function```

10. K Macdonald says:

Some very good comment on writing UDF's but some of us should have found the native StrReverse function by now! Mind you there are times that I find the standard string handling function set in VBA a little incomplete and may have started with a pre-conceived view.

11. Chandrashekhar Joshi says:

Thanks Hui. You are real HELP. God bless you and chandoo.

12. SyedGJ says:

Delicious!

 « Shortcut for Long Models Join Excel School & Become Awesome in Excel Today! »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.