CHALLENGE (and Ramblings on Palindromes)

[hatman]

As some of you may have figured out, I am a little bit of a nut when it comes to the english language... in High School I was dubbed a "Walking Lexicon", and rather than being offended, I took it as a compliment.

Anyway, my latest fascination has been with Palindromes formed of multiple words: A Man, A Plan, A Canal, Panama for example. I mean, we all know about Radar, Rotor, and Rotator, but multiple word palindromes are little bit more challengeing. As I started thinking about it, I started wondering if one couldn't write a simple Array formula to invert the order of a text string. As I started playing with it, I also saw potential uses in other applications, but I would need to solve this one first.

I came up with a solution, but it requires the use of a function having the ability to concatenate an ARRAY of values, rather than the CSV list that Concatenate() requires. This solution is fine for me, as I wrote such a UDF years ago (and doesn't MoreFunc have one too?), but I would still like to know if this is possible in a single formula using nothing but native standard Excel functions. I would like the the scheme to more easily be shared with people who do NOT have an addin containing an improved concatenation function.

Here is the CSE formula that I came up with: =Concat(MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)) where concat is defined as

Function concat(rnge, Optional delim As String = "") As String

    For Each Item In rnge
    
        If Item <> "" Then
        
            Output = Output & delim & Item
            
        End If
        
    Next Item
    
    concat = Right(Output, Len(Output) - Len(delim))

End Function

Can anyone come up with somethng better?

 

[Cbrine]

How about this hatman, it uses the character object of the cell.(Bonus is, you can then modify the properties of the actual character as well: Font, Color, etc...)

Private Sub CommandButton1_Click()
Dim reverse As String

For Count = 1 To ActiveCell.Characters.Count
  reverse = ActiveCell.Characters(Count, 1).Text & reverse
Next Count

MsgBox reverse
End Sub

HTH
Cal

PS-Just noticed the Native formula bit of the post, oh well, I will leave the code anyway.

 

[hatman]

Okay... but I would consider that a trivial solution, based on the condition of my challenge:

I would still like to know if this is possible in a single formula using nothing but native standard Excel functions

To be more specific, I am looking for a NON-VBA solution. I couldn't come up with a solution that satisfies this either... but as you point out, there are MANY solutions that do not satisfy these requirements.

 

[Aladin Akyurek]

See also:

http://www.mrexcel.com/archive2/68500/79422.htm

 

[hatman]

Good background, but again, not quite what I am looking for. I would like to be able to enter "Verbiage" is cell a1, and the formula in A2 will give the result of "egaibreV". Admittedly, this is not the easy way to go about the task of identifying Palindromes. However, the easy ways are trivial, and numerous and not much of a challenge.

So I am getting the sense that I was correct in my initial assumption that this cannot be done according to the rules of my challenge. Too often, when I make this assertion, someone like Aladin or Barry Houdini come up with a slick compact formula that I hadn't even thought of... but that doesn't seem to be happening here.

Anyone else?

 

[fairwinds]

Unfortunately, there is no "native" excel formula that allows you to concatenate an array so any "native" formula solution would be something like:

=CONCATENATE(MID(A1,LEN(A1)-0,1),MID(A1,LEN(A1)-1,1),MID(A1,LEN(A1)-2,1),MID(A1,LEN(A1)-3,1),MID(A1,LEN(A1)-4,1))

 

[hatman]

Thats' what I thought... but I wanted to get some corroboration... as I mentioned, just because I think something is a limitation does not mean that someone smarter than me hasn't figured out a way around it.

Thanx for your input fairwinds.

 

[Aladin Akyurek]

Good background, but again, not quite what I am looking for. I would like to be able to enter "Verbiage" is cell a1, and the formula in A2 will give the result of "egaibreV". Admittedly, this is not the easy way to go about the task of identifying Palindromes.

It looks to me that returning a truth-valued evaluation is cognitively the most satisfactory way of marking an entry as palindrome.

However, the easy ways are trivial, and numerous and not much of a challenge.

Aren't we downplaying it a bit here?

So I am getting the sense that I was correct in my initial assumption that this cannot be done according to the rules of my challenge. Too often, when I make this assertion, someone like Aladin or Barry Houdini come up with a slick compact formula that I hadn't even thought of... but that doesn't seem to be happening here.

Anyone else?

I'm very much satisfied with TEXTREVERSE (see Morefunc) Longre has programmed in a language that compiles to a faster running function.

 

[hatman]

Am I down-playing? ...perhaps. But all of the solutions proposed here were among the approaches I explored in the first ten minutes that I started playing with this topic. As per usual, your formulas are more compact than my own attempts, but the basic functionality is the same. Good bad or indifferent, I specified the rules in my initial post to exclude these solutions... not because I find them totally unacceptable, but because I had already thought of them, evaluated the pros and cons, and wanted to know if I had another choice in the matter.

If I had no other possible solutions in mind before posting, I would have posted this topic in the other board and asked for any help. On the contrary, I am looking for elegance (if it exists) not necessarily quick-and-dirty. And I have my reasons for wanting to reverse the string, rather than simply perform a logical test. Does Longre's function do it? YES! Most emphatically. But I prefer to avoid Addins or VBA when I distribute workbooks... I'll use either or both if it is the best solution, but it is difficult to evaluate for the best solution if you don't have all of the options at your fingertips... in this case the one option I was lacking was the one I asked for: a single formula to reverse a string using native excel functions only. I know you don;t like saying something can't be done, Aladin, but in this case it's okay... it's the answer I expect. But just because I expect it does not mean that someone won't prove me wrong.

 

[Aladin Akyurek]

Am I down-playing? ...perhaps. But all of the solutions proposed here were among the approaches I explored in the first ten minutes that I started playing with this topic. As per usual, your formulas are more compact than my own attempts, but the basic functionality is the same. Good bad or indifferent, I specified the rules in my initial post to exclude these solutions... not because I find them totally unacceptable, but because I had already thought of them, evaluated the pros and cons, and wanted to know if I had another choice in the matter.

If I had no other possible solutions in mind before posting, I would have posted this topic in the other board and asked for any help. On the contrary, I am looking for elegance (if it exists) not necessarily quick-and-dirty. And I have my reasons for wanting to reverse the string, rather than simply perform a logical test. Does Longre's function do it? YES! Most emphatically. But I prefer to avoid Addins or VBA when I distribute workbooks... I'll use either or both if it is the best solution, but it is difficult to evaluate for the best solution if you don't have all of the options at your fingertips... in this case the one option I was lacking was the one I asked for: a single formula to reverse a string using native excel functions only. I know you don;t like saying something can't be done, Aladin, but in this case it's okay... it's the answer I expect. But just because I expect it does not mean that someone won't prove me wrong.

My comments and the link posted pertains directly to your problem description that mentions "palindromes": Is the strring X a plaindome?

Just reversing X doesn't look like an answer one would expect.

Hence the comment: "It looks to me that returning a truth-valued evaluation is cognitively the most satisfactory way of marking an entry as palindrome."

But if you reformulate your challenge to:

"Is there a formula constructed with just bult-in functions Excel currently command?"

The answer (my answer anyway) would be short: No.