Reverse Numbers

[Twalton]

I have a column of fields that I need to reverse the order by every 2 digits.

Current: 021598 Need: 981502 So last 2 digits, next 2, then first 2

Also if the current number only has 5 digits, it needs to do the following,

Current: 21598 Need: 98152 So last 2 digits, next 2, then first 1

Thanks in advance for any help I can get on this!

 

[jproffer]

Excel 2013/2016

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]021598[/TD]
[TD="align: right"]982102[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]21598[/TD]
[TD="align: right"]98152[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(LEN(A1)=5,RIGHT(A1,2) & MID(A1,2,2) & LEFT(A1,1),RIGHT(A1,2) & MID(A1,2,2) & LEFT(A1,2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Like that?

By the way, please note that the value in A1 is a text format due to the leading zero. But it still works either way.

 

[Twalton]

Thanks for the fast response. When I apply the formula, this is what I get.

[TABLE="width: 159"]
<tbody>[TR]
[TD="width: 95, align: right"]123456 [/TD]
[TD="width: 64"]562312[/TD]
[/TR]
</tbody>[/TABLE]


The middle 2 numbers are off.

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function RevDigits(ByVal S As String) As String
  Dim X As Long
  If Len(S) Mod 2 = 1 Then S = Application.Replace(S, 2, 0, " ")
  For X = Len(S) To 1 Step -2
    RevDigits = RevDigits & Mid(S, X - 1, 2)
  Next
  RevDigits = Trim(RevDigits)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RevDigits just like it was a built-in Excel function. For example,

=RevDigits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Twalton]

In the VB code, how does it know what field to look up and then update?

=RevDigits(A1) doesn't seem to work.

 

[jproffer]

=IF(LEN(A1)=5,RIGHT(A1,2) & MID(A1,2,2) & LEFT(A1,1),RIGHT(A1,2) & MID(A1,3,2) & LEFT(A1,2))

Sorry...now it should work....but I thought that before so

 

[Rick Rothstein]

In the VB code, how does it know what field to look up and then update?

=RevDigits(A1) doesn't seem to work.

Is the number you want to reverse in cell A1? If not, change A1 to the address of the cell containing your number. If, on the other hand, your number is in cell A1, then you will need to describe "doesn't seem to work" for us.

 

[jproffer]

Ok, sorry about that. Busy day, and I got in too much of a hurry.

Not to take away from Rick's UDF, but the formula in post #6 does work.

Excel 2013/2016
	A	B	C
1	021598		981502
2	15643		43561
3	158756		568715
4	39587		87953
5	784159		594178
Sheet1

Cell Formulas
Range	Formula
C1	=IF(LEN(A1)=5,RIGHT(A1,2) & MID(A1,2,2) & LEFT(A1,1),RIGHT(A1,2) & MID(A1,3,2) & LEFT(A1,2))

 

[Marcílio_Lobão]

Twalton, Good afternoon.

(greetings from Brazil Rick and jproffer)

The Rick's UDF works without any problem.

Did you followed the instructions step by step?
"...HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RevDigits just like it was a built-in Excel function. For example,

=RevDigits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook..."

Supposing your data at A1.
Then... at B1 you say =RevDigits(a1)
Nothing more.

For me it's working.

I hope it helps.

 

[Rick Rothstein]

Not to take away from Rick's UDF, but the formula in post #6 does work.

The main reason I posted the UDF is in case the OP's numbers were not always 5 or 6 digits long. If they are always 5 or 6 digits long, this formula is a little bit shorter than your formula...

=IFERROR(RIGHT(A1,2)&MID(A1,LEN(A1)-3,2)&LEFT(A1,LEN(A1)-4),"")