CHALLENGE (and Ramblings on Palindromes)

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
ABCD
1dlro
2
3HelloWorld
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...

Code: 
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
ABCD
1InputStringOutputString
2HelloWorlddlroWolleH
Output


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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
Click to expand...

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


Code: 
=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
 
Welcome to the board hady!
Cool idea that certainly works, however not very practical :wink:
 
"...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
 
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
Click to expand...

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...
 
Re: Reverse String Formula

Code: 
=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))&" "))
Click to expand...

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

Code: 
=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?
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

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