macro to reverse numbers in a cell

[michaelsmith559]

I need help with a macro to reverse numbers in a cell. I am unsure of the best way to go about this. My first thought was a select case, but I am unsure. Here is what I am trying to do. I have a list of numbers starting in A2 and going on down the column. New data is added to the column a couple of times each week. Each number in the column is one of three lengths: 5, 6, or 7. For example: suppose A2=1003620, A3=134898, and A4=22575. I need the macro to calculate the length of the number in the cell and if it is a 5 digit number put the first two numbers to the right of the last three numbers, for a 6 digit number put the first three numbers to the right of last three numbers, and for a 7 digit number leave the first digit, but move the next three numbers to the right of the last three numbers. So using the example above the macro would change the numbers like so:
A2=1003620 To A2=1620003
A3=134898 To A3=898134
A4=22575 To A4=57522

Thanks for any help.

Mike

 

[Scott T]

Try

Sub rearrangenum()

Dim lr As Long
Dim mylen As Long


lr = Cells(Rows.Count, 1).End(xlUp).Row


For x = 2 To lr
    mylen = Len(Cells(x, 1))
    If mylen = 5 Then Cells(x, 2) = Mid(Cells(x, 1), 3, 3) & Left(Cells(x, 1), 2)
    If mylen = 6 Then Cells(x, 2) = Mid(Cells(x, 1), 4, 3) & Left(Cells(x, 1), 3)
    If mylen = 7 Then Cells(x, 2) = Left(Cells(x, 1), 1) & Right(Cells(x, 1), 3) & Mid(Cells(x, 1), 2, 3)


Next x




End Sub

 

[RatExcel]

Try this:

Function ReverseNumbers(strNum As String) As Long

  a = Left(strNum, IIf(Len(strNum) >= 6, Len(strNum) - 6, 0))
  b = Right(strNum, 3)
  c = Left(Right(strNum, 6), IIf(Len(strNum) >= 6, 3, IIf(Len(strNum) >= 3, Len(strNum) - 3, 0)))
  
  ReverseNumbers = a & b & c

End Function

 

[michaelsmith559]

Thanks for the replies. After looking at both options, I chose to go with the udf. Again, thanks both of you.