Sorting specific data

[virfir97@yahoo.com]

X-No-Archive: yes

I need help to sort the contents of a cell.
Cell A1= 01201

I wish to sort the numbers in A1 into descending numerical order. The
result should be =21100

What is the simplest formula that I can use to achieve this? I would
like to learn how to do this.

Thamks

[Ron Rosenfeld]

On 10 Jun 2005 03:56:44 -0700, virfir97@yahoo.com wrote:

>X-No-Archive: yes
>
>I need help to sort the contents of a cell.
>Cell A1= 01201
>
>I wish to sort the numbers in A1 into descending numerical order. The
>result should be =21100
>
>What is the simplest formula that I can use to achieve this? I would
>like to learn how to do this.
>
>Thamks

I think the simplest method is with a User Defined Function.

One problem you will run into is that if the cell is not pre-formatted as TEXT,
or if you do not precede your entry with a single quote, Excel will interpret
your entry as a number, and drop the leading zeros.

You can overcome this either by formatting as text; preceding entry with a
single quote ('), or using a formula that converts the number into a text
string.

Below is a UDF to do the sorting and return the result in an adjacent cell. To
enter it, <alt-F11> opens the VB Editor. Ensure your project is highlighted in
the Project Explorer window, then Insert/Module and copy/paste the code below
into the window that opens.

To use the UDF, enter the formula =sortstring(A1) in some cell.

==================================
Option Explicit

Function SortString(str As String) As String
Dim i As Long
Dim StrArray() As String

ReDim StrArray(1 To Len(str))

For i = 1 To Len(str)
StrArray(i) = Mid(str, i, 1)
Next i

SortString = Join(RevBubbleSort(StrArray), "")

End Function

Private Function RevBubbleSort(TempArray As Variant)
Dim temp As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = LBound(TempArray) To UBound(TempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) < TempArray(i + 1) Then
NoExchanges = False
temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = temp
End If
Next i
Loop While Not (NoExchanges)
RevBubbleSort = TempArray
End Function
===============================
--ron

[virfir97@yahoo.com]

X-No-Archive: yes

Thanks Ron for the UDF, it will be very useful.

Just out of curiosity what is the most basic formula that can achieve
the above solution? Or is there a online tutorial?

In this case, I really did want to learn and understand.
Thanks.

[Ron Rosenfeld]

On 10 Jun 2005 05:57:58 -0700, virfir97@yahoo.com wrote:

>X-No-Archive: yes
>
>Thanks Ron for the UDF, it will be very useful.
>
>Just out of curiosity what is the most basic formula that can achieve
>the above solution? Or is there a online tutorial?
>
>In this case, I really did want to learn and understand.
>Thanks.

I'm not sure what you mean by "most basic formula".

Another approach, though, would be to use the data/text-to-columns wizard to
split the number into individual cells. Then sort those cells horizontally,
descending; and finally, concatenate the result back into a single cell.

That approach is not strictly a formula and it would take longer to do
manually. It would take up room on the worksheet. In addition, if you
automated it via a macro, you would still have the issue of dealing with
variable length strings.


--ron