Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]

Posted on August 12th, 2011 in Excel Howtos , Learn Excel - 20 comments

The other day, while I was putting my kids to sleep, this idea came to me. How do I check if a cell contains a palindrome, using Excel formulas?

Next morning, I wrestled with excel for about 20 minutes and boom, the formula is ready.

Here is how it works:

If you enter a word or phrase in column B, it would tell you whether it is a palindrome or not.

But what is a palindrome?

A palindrome is a word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!

[definition from palindromelist.net]

So, to check if a cell contains palindrome, we need to reverse the cell contents and see if both original and reverse are the same.

For example if B1 contains MAN, then the reverse would be NAM and hence MAN is not a palindrome.

Check if a Word or Phrase is Palindrome using Excel Formulas

But how do we write a formula to check if a cell has palindrome?

  1. Assuming B1 contains the word (or phrase), the first step is to clean it. That means, we need to remove any spaces, commas, exclamation marks & other punctuation symbols. So a phrase like “Cigar? Toss it in a can. It is so tragic.” would become “CigarTossitinacanItissotragic”.
  2. The next step is to match this cleaned text (lets say this will be C1) with the reverse of it.
  3. But there is no reverse formula. So we use MID() to extract one letter at a time and match it with the corresponding letter from end. (ie first letter with last letter, second letter with second last letter etc.)
  4. To do this, we use, MID(C1,ROW(OFFSET($A$1,,,LEN(C1))),1 = MID(C1,LEN(C1)-ROW(OFFSET($A$1,,,LEN(C1)))+1,1)
  5. The left portion of this formula would give individual letters in C1 in left to right order and the right portion would give same in reverse order.
  6. We wrap this in a lovely SUMPRODUCT formula so that we can check for palindrome-ness of B1 using =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!")

How does this formula work?

Well, that is your weekend homework. Go figure.

One more homework if you are game

If you feel like playing with words, here is another challenge.

How would you test if a cell contains alliteration?
(Alliteration here is defined as sentence where all words begin with same letter)

Go ahead and post your answers using comments.

Download Palindrome Test Excel Workbook

Click here to download the excel workbook and see the palindrome test formulas yourself.

Learn more about Excel Array Formulas

Array formulas are a special class of Excel formulas that can provide powerful results with little work. We have a huge collection of array formula examples on chandoo.org. Go thru below list and see how deep the rabit hole goes.

SUMPRODUCT Formula and how to use it
Advanced SUMPRODUCT Queries
Use Array Formulas to check if a list is sorted
Calculating sum of digits in a number using formulas
Check if a number is Prime using array formulas
More… Excel Array Formulas – Examples & Demos

PS: Monday is our (Indian) Independence Day. So I will see you again on Tuesday.

PPS: On Tuesday, we will be announcing our Excel Formula Crash Course. Get ready.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

20 Responses to “Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]”

  1. Elias says:

    On Chandoo’s file select cell A1 and insert a row to see the error you get. Here is one way to avoid the error and the volatility of the formula.

    =IF(SUMPRODUCT((MID(C4,ROW(INDEX(A:A,1):INDEX(A:A,LEN(C4))),1)=MID(C4,LEN(C4)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(C4)))+1,1))+0)=LEN(C4),”It’s a Palindrome”, “Nah!”)

    Regards

  2. Luke M says:

    Here’s my attempt at the alliteration formula:
    =IF((LEN(A2)-LEN(SUBSTITUTE(A2,” “&LEFT(A2,1),””)))/2=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)),”Aha! An alliteration!”,”Nah!”)

  3. Luke M says:

    Oops, forgot SUBSTITUTE is case-sensitive. Retry:
    `
    =IF((LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),” “&LEFT(UPPER(A2),1),””)))/2=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)),”Aha! An alliteration!”,”Nah!”)
    `

  4. bfraser says:

    Putting your little ones to bed and thinking about Excel is a sure sign you need to have an “Excel Workshop” in Tahiti or any other favorite vacation spot.

  5. Michael Azer says:

    @Chandoo, your method to show if it is a palindrome, is an awesome tutorial to how different functions work. I put it in excel and followed it with the “Evaluate Formula” button. I fell in love with the “Row” trick and how you used it with the offset formula. Very Impressive!!!

  6. chrisham says:

    @ Luke M, that’s a clever formula! I tried unsuccessfully for over an hour…. sigh!

  7. Stephen says:

    @Luke
    NICE!

    one very minor technical point. is this an alliteration? “Car”… I would wrap your formula in a basic if… =if(iferror(find(” “,A2,1)),”Must contain more than 2 words!”; [yourformula])

  8. Luke M says:

    @Stephen
    Good point. I guess I made an assumption, and it came back to bite me. =P

  9. Michael Azer says:

    @Luke M – Very cool formula!
    Chandoo’s post and Luke’s comment have been a deep dive for me into formulas using text.

  10. Stephen says:

    I can see the confusion, above is the description of a palindrome which includes in the desription …a word…
    from dictionary.com ( i don’t understand 1 but 2 is valid!)
    alliteration [uh-lit-uh-rey-shuhn]
    1. the commencement of two or more stressed syllables of a word group either with the same consonant sound or sound group (consonantal alliteration), as in from stem to stern, or with a vowel sound that may differ from syllable to syllable (vocalic alliteration), as in each to all. Compare consonance ( def. 4a ) .
    2. the commencement of two or more words of a word group with the same letter, as in apt alliteration’s artful aid.

  11. Ghazanfar J says:

    beautiful!

  12. Luke M says:

    @Stephen
    I think 1 refers to words like:
    Bobby (bob-by), turtle (tur-tle), werewolf (were-wolf)
    All of those are a single words whos individual sylables start with same sound. However, I have no idea how you would get XL to test this. =P

  13. Joe Carsto says:

    Is the SUMPRODUCT function in the original Palindrome file really necessary? I used the formula below and achieved the same results.

    =IF(MID(C4,LEN(C4)-ROW(OFFSET($A$1,,,LEN(C4)))+1,1)= MID(C4,ROW(OFFSET($A$1,,,LEN(C4))),1), “It’s a Palindrome”,”Nah!”)

  14. Drazen says:

    The sam logic: =IF(SUM((MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=MID(B1,LEN(B1)-ROW(INDIRECT(“1:”&LEN(B1)))+1,1))*1)=LEN(B1),”Palindrome”,”Not palindrome”) –> Array entered
    @ Joe
    I agree, there is no need to use nor SUMPRODUCT neither SUM, because if every single comparison is TRUE it results to TRUE.

    @ Luke
    Congratulations, very clever formula! I didn’t suceed.

  15. Paul says:

    My attempt at the alliteration problem (assuming text is in A1)

    =IF(ISERROR(FIND(” “,SUBSTITUTE(UPPER(A1),” “&LEFT(UPPER(A1)),”x”))),”Alliteration”, “Not Alliteration”)

  16. Paul says:

    An average attempt at an alliteration assignment:

    =IF(ISERROR(FIND(” “,SUBSTITUTE(UPPER(A1),” “&LEFT(UPPER(A1)),”x”))),”Alliteration”, “Not Alliteration”)

  17. John S says:

    I don’t know if UDFs are excluded from this particular topic, and normally I’m all for using regular Excel before resorting to VBA, but there is a REVERSE function in VBA, so it’s a snip to create a UDF to reverse strings in workbooks. I think on this occasion, it’s worth it!

    I had this already, I have it saved in my Personal.xls:

    ‘ UDF to reverse a string, using a VBA command that doesn’t have an Excel function equivalent.

    ‘ =PERSONAL.XLS!ReverseString(string)

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

    Public Function ReverseString(Text As String)
    ReverseString = StrReverse(Text)
    End Function

  18. [...] wrote a post in August 2011 where he looked at determining if a cell contained a [...]

  19. s
    t
    r
    a
    w
    w
    a
    r
    t
    s

    strawwarts
     
     
     
     
     
     
     
     
     

    using text to column & offset function

     
     
     
     
     
     
     
     
     

    s
    t
    r
    a
    w
    w
    a
    r
    t
    s

    OFFSET($K$3,0,COUNTA($L$3:$U$3)+1-MATCH(L3,$L$3:$U$3,0))
     
     
     
     

    strawwarts
     
     
     
     
     
     
     
     
     

    TRUE
    Palindrome
     
     
     
     
     
     
     
     

  20. HAHAHA says:

    Alliteration formula modified

    =IF(ISBLANK(A1),”Enter some Text first”,IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))<=1,"Min. 3 words please", IF((LEN(A1)-LEN(SUBSTITUTE(UPPER(A1)," "&LEFT(UPPER(A1),1),"")))/2=LEN(A1)-LEN(SUBSTITUTE(A1," ","")),"Aha!An alliteration!","Nah!")))

Leave a Reply