Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VALUE)
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 InsertModule. 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 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VALUE)
Try
=VALUE(RevStr(cellref)) HTH, Bernie MS Excel MVP "3kings" wrote in message ... 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 InsertModule. 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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VAL
Nope, that didnt work either... :-(
"Bernie Deitrick" wrote: Try =VALUE(RevStr(cellref)) HTH, Bernie MS Excel MVP "3kings" wrote in message ... 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 InsertModule. 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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VAL
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? "3kings" wrote: Nope, that didnt work either... :-( "Bernie Deitrick" wrote: Try =VALUE(RevStr(cellref)) HTH, Bernie MS Excel MVP "3kings" wrote in message ... 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 InsertModule. 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 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VALUE)
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? "3kings" wrote: 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 InsertModule. 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 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subject: How to Reverse Contents in an Excel cell (NUMERIC VALUE) | Excel Worksheet Functions | |||
How to Reverse Contents in an Excel cell (Text or a Number) | Excel Worksheet Functions | |||
NEW QUEST/REQ in "How to Reverse Contents ..." Pls Respond? TIA | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
Using VB, specific cell data into email subject | Excel Discussion (Misc queries) |