CHALLENGE (and Ramblings on Palindromes)

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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
Code:
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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...)

Code:
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,141,626
Messages
5,707,489
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top