Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re-order a 4 digit number (2K3)

    In column A I have a series of 4 digit numbers. I would like to re-order these numbers into numerical order within themselves and place it in B

    ie 3241 =1234 or 2431=1234

    Any ideas?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-order a 4 digit number (2K3)

    Here is a custom function:

    Function SortDigits(MyNum)
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
    Dim c As String
    n = Len(MyNum)
    For i = 1 To n - 1
    For j = i + 1 To n
    If Mid(MyNum, i, 1) > Mid(MyNum, j, 1) Then
    c = Mid(MyNum, i, 1)
    Mid(MyNum, i, 1) = Mid(MyNum, j, 1)
    Mid(MyNum, j, 1) = c
    End If
    Next j
    Next i
    SortDigits = MyNum
    End Function

    It will return a string. If you prefer a number, use

    SortDigits = Val(MyNum)

    but keep in mind that leading zeros will be lost.

    Use the function like this in a formula in B2:

    =SortDigits(A2)

    and fill down.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Re-order a 4 digit number (2K3)

    Thanks

    A real hammer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I am getting #NAME? though!
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-order a 4 digit number (2K3)

    Where did you paste the macro? It should be in a standard module in the workbook where you want to use it (not in a worksheet module or in ThisWorkbook), or in a standard module in your Personal.xls. In the latter case, you must use it as

    =Personal.xls!SortDigits(A2)

    Oh yes, the module should not be named SortDigits too - that confuses VBA.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Re-order a 4 digit number (2K3)

    Hey Bear, the UDF worked for me without any problems. I used the "SortDigits = Val(MyNum)" version so the result would be numbers and not text. By the way (and I know you know this), if you use a custom format for the cells: <font color=blue> 0000 </font color=blue> , you won't have to worry about the leading zeroes being left off.
    - Ricky

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Re-order a 4 digit number (2K3)

    Thanks Ricky and Hans

    It was a rather late night (and rather bizarre) moment of inspiration when I had a eureka moment on something and I hadn't thought about the Module and lazily went for the View Code option on the worksheet tab <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    As for the formatting I have just stored the number as text and the UDF works fine..thanks again.

    This little option is phase 1 of the project and it will have 3 phases to sieve the numbers <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Re-order a 4 digit number (2K3)

    And here is the beautiful array formula version <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    =SMALL(VALUE(MID(A1,{1,2,3,4},1)),1)&SMALL(VALUE(M ID(A1,{1,2,3,4},1)),2)&SMALL(VALUE(MID(A1,{1,2,3,4 },1)),3)&SMALL(VALUE(MID(A1,{1,2,3,4},1)),4)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •