How to Reverse Contents in an Excel cell (Text or a Number)

[CeeGee]

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

 

[Gord Dibben]

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

 

[CeeGee]

Thanks very much Gord, the solution you provided is very nifty. I have over
30,000 rows of data which I can now apply this to.

Maybe Microsoft will provide this as a standard function one day.

Thanks again.

 

[Gord Dibben]

Thanks for the feedback.

Note that if your numerics end in zeros like 123400 the 00 will be dropped if
you use the =RevStr(cellref)*1

To keep the zeros you must change them to text per the =RevStr(cellref).


Gord

 

[3kings]

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]

My attempt at deciphering...........your use of text instead of numerics in your
example adds difficulty.

I will assume ABC, CBA and XYZ are numbers. If they are not then why would you
attempt to use the "+" sign?

A1 contains 987

B1 contains =revstr(A1)*1..............789

C1 contains =A1+B1.................1776

A2 contains =C1..................1776

B2 contains =revstr(C1)*1...............6771

"etc." can lead anywhere.


Gord

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?

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