Reversing a cell

Opinicus

unread,

Feb 20, 2004, 2:11:07 PM2/20/04

to

I need a function that will reverse the data in a cell. For example if the
value in a cell A1 is "xyzzy", then REV(A1) would be yzzyx. If the value in
cell Z19 is "32767" then REV(Z19) would be 76732.

Is this possible?

--
Bob
Kanyak's Doghouse
http://kanyak.com

Frank Kabel

unread,

Feb 20, 2004, 2:15:47 PM2/20/04

to

Hi
try the following (without much error checking, etc.)

Public Function rev(rng As Range)
Dim return_str As String
Dim i As Integer
application.volatile
return_str = ""
With rng
For i = Len(.Value) To 1 Step -1
return_str = return_str & Mid(.Value, i, 1)
Next i
End With
rev = return_str
End Function

--
Regards
Frank Kabel
Frankfurt, Germany

A.W.J. Ales

unread,

Feb 20, 2004, 2:25:00 PM2/20/04

to

Opinicus,

Look for the (free) ASAP addin : http://www.asap-utilities.com/
It contains a text function which does exactly what you ask.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Opinicus" <gez...@spamcop.net> wrote in message
news:103cmui...@news.supernews.com...

Ken Wright

unread,

Feb 20, 2004, 2:28:24 PM2/20/04

to

What version Excel:-

If prior to 2000 then:-

Function ReverseText1(cell)
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(cell)
For i = TextLen To 1 Step -1
ReverseText = ReverseText & Mid(cell, i, 1)
Next i
End Function

else:-

Function ReverseText(cell)
ReverseText = StrReverse(cell.Text)
End Function

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Opinicus" <gez...@spamcop.net> wrote in message
news:103cmui...@news.supernews.com...

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.592 / Virus Database: 375 - Release Date: 18/02/2004

A.W.J. Ales

unread,

Feb 20, 2004, 2:51:50 PM2/20/04

to

Frank,

Your function - as I see it - will indeed work, but I think that you could
delete the line : Application.volatile.
It causes the function to be recalculated each time the sheet is
recalculated, even if the cell the function depends on isn't changed.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Frank Kabel" <frank...@freenet.de> wrote in message
news:OX9$zX%239DH...@TK2MSFTNGP10.phx.gbl...

Frank Kabel

unread,

Feb 20, 2004, 2:54:09 PM2/20/04

to

Hi Auk (hope I got your name right)
yes you're right. The line is just the rest of a formula I copied for
this specific purpose (#!"§$ copy and paste).
So for the OP: Delete the line :-)

--
Regards
Frank Kabel
Frankfurt, Germany

A.W.J. Ales wrote:
> Frank,
>
> Your function - as I see it - will indeed work, but I think that you
> could delete the line : Application.volatile.
> It causes the function to be recalculated each time the sheet is
> recalculated, even if the cell the function depends on isn't changed.
>
>

A.W.J. Ales

unread,

Feb 20, 2004, 3:04:04 PM2/20/04

to

Hi Frank,

No need for : (#!"§$ copy and paste).
I do it myself also all the time (and sometimes wrong as well !!).
And Yes : You had the name correct.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Frank Kabel" <frank...@freenet.de> wrote in message
news:OxiDQt%239DH...@TK2MSFTNGP10.phx.gbl...