Re: Sorted Fixed Length String

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • Re: Byte Array to String
    ... retrieved text will mismatch the original characters. ... encoding the characters. ... Dim strFileData as String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: to remove all except hex characters and then validate
    ... Douglas' function allowed unlimited characters that would then strip out all ... Public Function OnlyHexCharacters(InputString As String) As String ... Dim strCurrChar As String ... Dim strOutput As String ...
    (microsoft.public.access.formscoding)
  • HTMLEncode: low surrogate char Error
    ... UTF characters that are part of the default windows code page throw an ... you will avoid throwing the exception. ... Dim _textStreamReader As StreamReader ... Function GetResource(ByVal ResourceName As String) ...
    (microsoft.public.dotnet.general)
  • Re: Linking from a mailing address in the database to maps.google.
    ... > unsafe, it was inputting the hex value, which wasn't working. ... > with this particular event procedure, given the limited characters ... >>> Dim stAddress As String ...
    (microsoft.public.access.formscoding)
  • Re: How to make space between bars in a graph?
    ... only one or two characters to replace. ... Private Function StringFilter(Strng As String) ... Dim FilteredStrng As String, n As Integer ...
    (microsoft.public.access.modulesdaovba)