Subject: How to Reverse Contents in an Excel cell (NUMERIC VALUE)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

3kings - 13 Sep 2007 17:24 GMT

Ok, followed this in an Excel2003 workbook, and got ALMOST there. I was able
to get the reverse returned, but then adding the *1 returned a #VALUE! error.
Clicking the error tab did not give me the option to Convert To Number,
either.
If its any help, this is what I want to accomplish:
start with a number:
(ABC)
add its reverse (here is where I need the function)
ABC+CBA = XYZ
So:
A1 is (ABC)
B1 is (CBA)
C1 is A2 + B2 [XYZ]

THEN -
A2 = (XYZ)
B2 = (ZYX)

etc.

Any suggestions?

"Gord Dibben" wrote:

> CeeGee
>
> By using a user defined function.
>
> Public Function RevStr(Rng As Range)
> RevStr = StrReverse(Rng.text)
> End Function
>
> Usage is: =RevStr(cellref)
>
> If the data is numeric...... =RevStr(cellref)*1 which forces it back to
> numeric.
>
> This function would be copied to a general module in your workbook.
>
> If not familiar with macros and VBA, visit David McRitchie's website on
> "getting started".
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> In the meantime..........
>
> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
> Hit CRTL + R to open Project Explorer.
>
> Find your workbook/project and select it.
>
> Right-click and Insert>Module. Paste the above code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Gord Dibben Excel MVP
>
> On Fri, 20 May 2005 15:16:02 -0700, CeeGee <CeeGee@discussions.microsoft.com>
> wrote:
>
> >If I have the following text string in a cell "abc123.xyz" how do I reverse
> >the contents i.e. "zyx.321cba"?
> >
> >If the cell contains a numeric value e.g. 12345, then I want to know how to
> >reverse it i.e. 54321

Reply to this Message

Bernie Deitrick - 13 Sep 2007 17:55 GMT

Try

=VALUE(RevStr(cellref))

HTH,
Bernie
MS Excel MVP

> Ok, followed this in an Excel2003 workbook, and got ALMOST there. I was able
> to get the reverse returned, but then adding the *1 returned a #VALUE! error.
[quoted text clipped - 61 lines]
>> >If the cell contains a numeric value e.g. 12345, then I want to know how to
>> >reverse it i.e. 54321

Reply to this Message

3kings - 13 Sep 2007 18:38 GMT

Nope, that didnt work either... :-(

> Try
>
[quoted text clipped - 69 lines]
> >> >If the cell contains a numeric value e.g. 12345, then I want to know how to
> >> >reverse it i.e. 54321

Reply to this Message

Duke Carey - 13 Sep 2007 18:54 GMT

both

=1*RevStr(cellref)

and

=VALUE(RevStr(cellref))

work for me.

What happens when you try? And where did you place the function code that
Gord provided?

> Nope, that didnt work either... :-(
>
[quoted text clipped - 71 lines]
> > >> >If the cell contains a numeric value e.g. 12345, then I want to know how to
> > >> >reverse it i.e. 54321

Reply to this Message

Sandy Mann - 13 Sep 2007 19:08 GMT

didn't work is a very helpful reply. Was the reason like me you don't have
the "StrReverse" keywork in VBA?

If so try:

Public Function RevStr(Rng As Range)
For cX = Len(Rng) To 1 Step -1
sStr = sStr & Mid(Rng, cX, 1)
Next cX
RevStr = sStr
End Function

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Nope, that didnt work either... :-(
>
[quoted text clipped - 80 lines]
>> >> >how to
>> >> >reverse it i.e. 54321

Reply to this Message

pdberger - 14 Sep 2007 01:02 GMT

3kings --
I can see how to do it w/out VBA if all your original numbers have the same
number of digits. However, if they have different numbers of digits, then I
suspect it would take a VBA snippet.

Idle curiosity -- why are you trying to do this?

> Ok, followed this in an Excel2003 workbook, and got ALMOST there. I was able
> to get the reverse returned, but then adding the *1 returned a #VALUE! error.
[quoted text clipped - 61 lines]
> > >If the cell contains a numeric value e.g. 12345, then I want to know how to
> > >reverse it i.e. 54321

Reply to this Message