CHALLENGE (and Ramblings on Palindromes)

[Todd Bardoni]

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.xls
	A	B	C	D
1	d	l	r	o
2
3	HelloWorld
4
5	#########
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.xls
	A	B	C	D
1	InputString	OutputString
2	HelloWorld	dlroWolleH
Output

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

 

[shades]

A little more on mathematical palindromes:

Palindrome records and sequencing

World of Numbers - including specialized Palindromes

 

[erik.van.geit]

this is very intriguing (if this word exists)

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

 

[hady]

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]

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

 

[Richard Schollar]

Hey Hady

Fairwinds is right - that is sweet! Good one

Richard

 

[abimono]

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

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

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]

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]

"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