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

1. ## 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?

2. ## 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. ## 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!

4. ## 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. ## 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.

6. ## 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>

7. ## 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)

#### Posting Permissions

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