fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
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.

Facebook
Twitter
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

Excel School made me great at work.
5/5

– Brenda

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

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

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

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

27 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")

  24. Scott says:

    I can check if palindrome without array functions or vba, but it uses three extra columns to check.

    First trim out the spaces between possible words: =SUBSTITUTE(A2," ","") I put this at C1

    I

    Next pull this function as far down as needed: =if(E1="","",row(A1)) It just gives each character a number value. I put this in the D column.

    Next column will be this: =mid($C$1,row(A1),1) This will give us each letter in order. I put this in the E column.

    Last column will be: =if(E1=iferror(VLOOKUP(MAX(D:D)-(ROW(A1)-1),D:E,2),""),0,1) This checks to see if the last letter is the same as the first letter. In the next row it checks to see if the second to last is the same as the second, as so on. I put this in column F

    So the answer will be "Yes" if the sum of column F=0, and "No" if it's not. =If(SUM(F:F)=0,"Yes, Palindrome!","Not Palindrome")

  25. Simon says:

    Bit late responding, but my alliteration formula was...

    {=IF(SUM(IF(MID(A28,ROW(OFFSET(A1,,,LEN(A28))),2)=" "&LEFT(A28,1),1,0))=LEN(A28)-LEN(SUBSTITUTE(A28," ","")),"Alliterative","Nope!")}

    (I assumed the exercise was about using the curly brackets, so came up with a way that did!)

Leave a Reply