# CHALLENGE (and Ramblings on Palindromes)



## hatman (Sep 15, 2006)

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 (Sep 15, 2006)

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 (Sep 15, 2006)

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 (Sep 15, 2006)

See also:

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


----------



## hatman (Sep 18, 2006)

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 (Sep 18, 2006)

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 (Sep 18, 2006)

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 (Sep 18, 2006)

> 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 (Sep 19, 2006)

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 (Sep 19, 2006)

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


----------



## Todd Bardoni (Sep 19, 2006)

A post of this nature from not too long ago:

http://www.mrexcel.com/board2/viewtopic.php?p=1112255#1112255

I rather liked my solution and can be adapted for strings up to 256 characters.

Edit:-

Here it is adapted for 256 characters:Book1.xlsABCD1dlro23HelloWorld45#########6#########Formulas

Named Formula:-
str_ReversTxt (entered in A1:IV1 with ctrl/shift/enter):- =LEFT(RIGHT(Formulas!$A$3,COLUMN(Formulas!1:$256)),1)

A3:- =Output!A2

A5:- =$A$1&$B$1&$C$1...etc

A6:- =$DH$1&$DI$1&$DJ$1...etc

I wrote a macro to write the last two concat formulas above...


```
Sub CreateConcatFormu()
    For x = 1 To 111
        t = Cells(1, x).Address
        i = i & t & "&"
    Next x
    Range("a5") = "=" & Left(i, Len(i) - 1)
    t = ""
    i = ""
    For x = 112 To 256
        t = Cells(1, x).Address
        i = i & t & "&"
    Next x
    Range("a6") = "=" & Left(i, Len(i) - 1)
End Sub
```
Book1.xlsABCD1InputStringOutputString2HelloWorlddlroWolleHOutput

str_Output:- =LEFT(Formulas!A5&Formulas!A6,LEN(Output!$A$2))


----------



## shades (Sep 22, 2006)

A little more on mathematical palindromes:

Palindrome records and sequencing

World of Numbers - including specialized Palindromes


----------



## erik.van.geit (Sep 22, 2006)

this is very intriguing (if this word exists)

http://en.wikipedia.org/wiki/Sator_Arepo_Tenet_Opera_Rotas


----------



## hady (Sep 30, 2006)

*Reverse String Formula*



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



Hatman, I can break your challenge by only using simple worksheet function, no VBA. but you must set option/calculation/iteration fill maximum number to 1000. before inputing my formula otherwise circular validation error come up.

this number representing number of max letters that will be reversed, you could put 10 000 if you want.

put any sentece in cell A1, B1 type in below formula and enter as CSE



```
=IF(LEN(B1)<LEN(A1)+1,B1&MID(A1,LEN(A1)+1-LEN(B1),1),IF(MID(B1,1,1)<>"0",B1,RIGHT(B1,LEN(A1))&" "))
```

don't surprise if your screen only resulting "0?" at the first CSE, press F9 to refresh and hold until you get the correct reverse string from A1 in cell B1

is that what you looking for mate? 

Hady - Indonesian
http://groups.yahoo.com/group/xl-mania


----------



## fairwinds (Oct 1, 2006)

Welcome to the board hady!
Cool idea that certainly works, however not very practical :wink:


----------



## Richard Schollar (Oct 1, 2006)

Hey Hady

Fairwinds is right - that is sweet!  Good one 

Richard


----------



## abimono (Oct 3, 2006)

"...however not very practical... "

i agree it's not very practial, but the "circular validation" technique could solve many problems, specially for the one who didn't understand about macro and some complicated array problems.

for example : 

1. hatman's case [of course]

2. multiple concaenate without addin / macro
    see : http://tech.groups.yahoo.com/group/XL-mania/message/6144
http://tech.groups.yahoo.com/group/XL-mania/message/6151
    illustration : input in cell B23 to B27, a series of string consist of 3
    characters. the asker want to multiple concatenate without vba
    solution : http://www.mail-archive.com/xl-mani...le_concatenante_using_circular_validation.xls

3. inserting space between character without vba
    input : "microsoft"
    output : "m i c r o s o f t"
    we could solve this without a vba, but using this circular validation
    [iteration] methods

4. multiple "solver tools" of a problem
    see : http://tech.groups.yahoo.com/group/XL-mania/message/6170
    illustration : input in column B2 to B1000, the asker want to show
    a 30 workdays [excluding saturday and sunday] after the date in 
    column B
    solution : set the iteration to 1000
    in cell D2 write : =NETWORKDAYS(B2,C2)
    in cell C2 write : =IF(D2<0,B2,IF(D2<30,C2+1,C2)) 
    drag D2 to C2 to D1000 and C1000

beautiful "batch solver" isn't it?


abimono
http://groups.yahoo.com/group/XL-mania


----------



## fairwinds (Oct 3, 2006)

Welcome to the board!



> 4. multiple "solver tools" of a problem
> see : http://tech.groups.yahoo.com/group/XL-mania/message/6170
> illustration : input in column B2 to B1000, the asker want to show
> a 30 workdays [excluding saturday and sunday] after the date in
> ...



You mean like: =WORKDAY(B2,30) :wink: 

Well maybe there can be cases where this can be useful but I do not think too often...


----------



## Todd Bardoni (Oct 3, 2006)

*Re: Reverse String Formula*



> ```
> =IF(LEN(B1)<LEN(A1)+1,B1&MID(A1,LEN(A1)+1-LEN(B1),1),IF(MID(B1,1,1)<>"0",B1,RIGHT(B1,LEN(A1))&" "))
> ```



Nice!  but I don't think you need to enter your formula with CSE.  Also, I *think* it can be shortened to (with one less calculation step and easier to cognitively process):


```
=IF(LEFT(B2,1)="0",RIGHT(B2&MID(A1,LEN(A1)-LEN(B2)+1,1),LEN(A1)),B2)
```

Also, how could you "reset" the formula for a new word without having to enter into edit mode?


----------



## abimono (Oct 4, 2006)

_"Welcome to the board! "_
thanx

_
"You mean like: =WORKDAY(B2,30)"_

yup! right, but without analysis toolpak installed

i agree to not using this method too often

just use it to break some challenge     

abimono 
http://groups.yahoo.com/group/XL-mania


----------



## fairwinds (Oct 4, 2006)

> yup! right, but without analysis toolpak installed


Not really as NETWORKDAYS also belongs to Analysis toolpack :wink: 

Anyway, I get your drift. Great work.


----------



## abimono (Oct 4, 2006)

*ops*

sorry... i forgot...   

assuming there's no analysis toolpak the formula in D2 should be 
{=SUM(N(MOD(ROW(INDIRECT(B2&":"&C2)),7)>1))}

   


abimono 
http://groups.yahoo.com/group/XL-mania


----------

