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!

 

[Haluk]

I am not sure why Mr.Rothstein didn't use the StrReverse function of VBA .... may be I'm missing something but, give this a try;

Function ReverseText(str As String) As String
    ReverseText = StrReverse(str)
End Function

 

[Rick Rothstein]

I am not sure why Mr.Rothstein didn't use the StrReverse function of VBA .... may be I'm missing something but, give this a try;

Function ReverseText(str As String) As String
    ReverseText = StrReverse(str)
End Function

I didn't use it because it does not return the value the OP said he wanted. StrReverse reverses everything whereas the OP said he wanted each pair of digits to stay in the correct order, but he wanted the pairs themselves reversed. For the number 784159, the OP wanted 594178 but your function returns 951487.

 

[Haluk]

I didn't use it because ........ the OP said he wanted each pair of digits to stay in the correct order, but he wanted the pairs themselves reversed.

Seems I haven't read the question at all

Thanks for the brief explanation.

 

[jproffer]

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),"")

Absolutely agree!

One of my (many) shortcomings with excel, and specifically answering questions for others, is that I take what they say/ask at very literal value. In this case, even though I know it's possible to have a length other than 5 or 6, I didn't even take into account that the OP may want something to handle that when and if it occurs.

As I learn myself, I try to expand my thinking, and in turn expand the possibilities of their situation in my own mind, but sometimes (like today) I get in a hurry, and so instead of giving a more "generic" (any number of digits) answer I sometimes fall back into my old ways and take their question ultra-literally.

To the OP, and I mean this sincerely, if I had asked the question and had the answers given here available to me, I would use the UDF since it will handle any length of string without error. I may make a slight change though. Again not to take away from Rick's idea, but I might add a second argument and make a variable for the number of digits to split (in this case, 2). Just an idea, and if you always need to split by 2, then it will certainly work as is.