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

Share

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. ### 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)

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.

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.

### 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

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.

### How to learn Python as an “Excel person”?

Python is a great programming language to learn for anyone in data profession. For people with prior Excel skills and a little bit of programming bent, Python

## Related Tips

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

• HR says:

how?

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!

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

' =PERSONAL.XLS!ReverseString(string)

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. ramzan shaikh says:

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 ?

• Markus says:

I tried to make an arrow, but it turned out to be a ? mark.
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!)

### Get FREE Excel & Power-BI Newsletter

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