fbpx

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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR

25 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!")))

  21. Markus says:

    Same goes for taco cat.
    T ?
    A ?
    C ?
    O ?
    C ?
    A ?
    T ?

  22. PINOY-exceler says:

    array formula (ctrl+shift+enter):

    =IF(AND((MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)=(MID(C1,(LEN(C1)+1)-ROW(INDIRECT("1:"&LEN(C1))),1)))),"It's a Palindrome","Nah!")

    sumproduct formula:

    =IF(SUMPRODUCT((INT((MID(C1,(LEN(C1)+1)-ROW(INDIRECT("1:"&LEN(C1))),1))=(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)))))=LEN(C1),"It's a palindrome","Nah!")

  23. Ufoo says:

    Hi gurus,

    I had derived the following formula before seeing PINOY-excelers's comment. My formula is not working though. Any ideas why? Thanks: =IF(SUMPRODUCT(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)=MID(C1,LEN(C1)-ROW(INDIRECT("1:"&LEN(C1)))+1,1))+0=LEN(C1),"Pallindrome","Nah")

Leave a Reply