A challenge

  • Thread starter Thread starter Jazzer
  • Start date Start date
J

Jazzer

Hi all you masterminds,

I've spent some time trying to solve this problem. Not exactl
because I need this, but because it's challenging. At least to me i
is.

So, can you make a worksheet function that reverses a string? That'
it basically.

- Asse
 
Yes, something like this. I haven't tried to code but..

Dim variable as string, Result as string
Dim Length as integer, x as integer

Length = len(variable)
x=Length
while x<>x-Length
Result = Result & mid(variable,x,1)
x=x-1
wend
msgbox Result



Cheers
Paa
 
Hi
I would use a UDF for this:
Function ReverseString(rng As Range) As String
Dim i As Integer
For i = Len(rng.Value) To 1 Step -1
ReverseString = ReverseString & Mid(rng.Value, i, 1)
Next
End Function

Just call this function with
=ReverseString(A1)
there A1 stores your source string

Frank
 
Hi Paal and thanks,

To make it a challenge, I ment that it should be a worksheet formula
not a VBA code. And to be more exact, it should be all in one cell. B
using multible cells or VBA it is an easy thing to do.

- Asse
 
Hi
the only thing I have come up with is the following array formula:
MID(A1,1+LEN(A1)-ROW(INDIRECT("1:" & LEN(A1))),1)

Unfortunately this requires the target to be an array with as many
columns/rows as the text lenght of A1. And CONCATENATE doe not work on
array (AFAIK). So getting this array result into one cell is the tricky
part.

Frank
 
Frank said:
*Hi
the only thing I have come up with is the following array formula:
MID(A1,1+LEN(A1)-ROW(INDIRECT("1:" & LEN(A1))),1)

Unfortunately this requires the target to be an array with as many
columns/rows as the text lenght of A1. And CONCATENATE doe not wor
on
array (AFAIK). So getting this array result into one cell is th
tricky
part.

Frank *
Click to expand...

Hi,

I have tried same kind of approach and have come up with the sam
problem, CONCATENATE doesn't work with arrays.

Maybe this is something that is impossible to do in Excel.

- Asse
 
Just to mention...in later versions of Excel, one can use:

Function StringReverse(s As String) As String
StringReverse = StrReverse(s)
End Function
 
So, can you make a worksheet function that reverses a string? That's
it basically.
Click to expand...

Combine these all on one line.
=choose(length(A1),
A1,
right(a1,1)&left(a1,1),
right(a1,1)&mid(a1,2,1)&left(a1,1),
right(a1,1)&mid(a1,3,1)&mid(a1,2,1)&left(a1,1),
....etc...

Only works for strings up to 29 characters.
 
Hi
Jonathan said:
Combine these all on one line.
=choose(length(A1),
A1,
right(a1,1)&left(a1,1),
right(a1,1)&mid(a1,2,1)&left(a1,1),
right(a1,1)&mid(a1,3,1)&mid(a1,2,1)&left(a1,1),
...etc...

Only works for strings up to 29 characters.
Click to expand...

:-) that is brute force
regards
Frank
 
Hi
Click to expand...

Actually the choose is unnecessary:
=MID(E59,10,1)&MID(E59,9,1)&MID(E59,8,1)&MID(E59,7,1)&MID(E59,6,1)&MID
(E59,5,1)&MID(E59,4,1)&MID(E59,3,1)&MID(E59,2,1)&LEFT(E59)

works for up to 10 characters
 
Back
Top