Re: Sorted Fixed Length String

On Wed, 18 May 2005 08:31:39 -0700, Paul Black
<Anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Hi Everyone,
>
>I have a String of Numerical Digits Created Using Concatenate.
>The Strings could be from 6 Characters in Length to 11 Characters in
>Length.
>The Most Characters in a String with a Digit GREATER than 1 can ONLY be
>6, the Remainder of the Digits will be 0.
>The Least Characters in a String with a Digit GREATER than 1 can ONLY be
>1, the Remainder of the Digits will be 0.
>
>Is there a Formula that will Produce a String of ONLY 6 Characters Long
>Please.
>Is it also Possible to Sort the String with the Highest Digit on the
>Left to the Lowest Digit on the Right Please.
>
>For Example :-
>11111100000 would Become 111111
>111120000 would Become 211110
>11103000 would Become 311100
>11220000 would Become 221100
>00510000000 would Become 510000
>123000 would Become 321000
>
>Thanks in Advance.
>All the Best.
>Paul
>

Here is a UDF. I may have some unnecessary conversions in there but it seems
to work with multiple formats.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
UDF below into the window that opens.

To use this, enter =FixNum(cell_ref) into some cell and it will do the
conversion as you describe.

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

Function FixNum(num As Double) As Double
Dim str As String
Dim i As Long
Dim TempArray

str = Replace(CStr(num), "0", "", 1, -1, vbTextCompare)
ReDim TempArray(Len(str) - 1)
For i = 0 To UBound(TempArray)
TempArray(i) = Mid(str, i + 1, 1)
Next i
BubbleSort TempArray
str = StrReverse(Join(TempArray, ""))
str = Left(str & "000000", 6)

FixNum = CDbl(str)

End Function

Private Function BubbleSort(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 = 0 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)

End Function

==================================


--ron
.

Loading