how do i find palindromes using excel?

  • Thread starter Thread starter Guest
  • Start date Start date
Find where? Are you asking if you can test if a string is a palindrome?
Or do you want to reverse a number, if the latter with the number in A1 use

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*10^(ROW(INDIRECT("1:"&
LEN(A1)))-1))
 
Hi,

This is an absolutely inelegant solution to your post, but see whether it
works for you (Excel experts would laugh at this formula)!

To check whether a given string (upto 16 characters) contained in cell A1 is
a palindrome use the following formula:

=A1=MID(A1,16,1)&MID(A1,15,1)&MID(A1,14,1)&MID(A1,13,1)&MID(A1,12,1)&MID(A1,11,1)&MID(A1,10,1)&MID(A1,9,1)&MID(A1,8,1)&MID(A1,7,1)&MID(A1,6,1)&MID(A1,5,1)&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1)

If the string is a palindrome the formula returns "TRUE", else "FALSE".
Note that the formula doesn't differentiate upper and lower cases. If the
string is a number, start with a ' character.

To reverse a string (upto 16 characters),
remove the "=A1" at the beginning of the formula.

Regards,
B. R. Ramachandran
 
Using VBA

Function Palindrome(strTest As String) As Boolean
Palindrome = Trim(strTest) = StrReverse(Trim(strTest))
End Function

See Chip Pearson's site for guidance on where to place the function's code
http://www.cpearson.com/excel/codemods.htm

Once you've got the code in the right place, utilize it like so:

with the word or phrase you want test in A1, put

=Palindrome(A1)

in B1. If it's a palindrome you'll see TRUE, otherwise FALSE
 
how do i find palindromes using excel?
Click to expand...

Here's one way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

With your test string in A1, the following formula will show TRUE if it is a
palindrome, and FALSE if it is not.

I assumed that you did NOT want to include punctuation, so that a string like:

"Madam, I'm Adam"

would test as true.

=REGEX.SUBSTITUTE(A1,"\W")=TEXTREVERSE(REGEX.SUBSTITUTE(A1,"\W"))

This will also work with numeric input. However, if the number being tested
has one or more leading zeros, it will have to be entered as TEXT.


--ron
 
Using VBA

Function Palindrome(strTest As String) As Boolean
Palindrome = Trim(strTest) = StrReverse(Trim(strTest))
End Function

See Chip Pearson's site for guidance on where to place the function's code
http://www.cpearson.com/excel/codemods.htm

Once you've got the code in the right place, utilize it like so:

with the word or phrase you want test in A1, put

=Palindrome(A1)

in B1. If it's a palindrome you'll see TRUE, otherwise FALSE
Click to expand...


It might be preferable to ignore punctuation and case as well as spaces.

Perhaps:

===================================
Function Palindrome(StrTest As String) As Boolean
Dim temp As String
Dim i As Long

For i = 1 To Len(StrTest)
If Mid(StrTest, i, 1) Like "[0-9A-Za-z]" Then
temp = temp & UCase(Mid(StrTest, i, 1))
End If
Next i

Palindrome = temp = StrReverse(temp)

End Function
==========================


--ron
 
Back
Top