Sorting characters within a string

mousie · Jan 6, 2008

Hey there!

I'm struggling at the moment to make a piece of VBA that will allow me "bubble sort" and re-arrange the internal characters within a string

E.g. String "BAA" will become "AAB"

Below is my feeble attempt at the VBA code for this

Public Function sortwdaz(txtstring1 As String)

Dim str1Len As Integer
Dim ctr As Long
Dim currentpos As Single
Dim tempstr As String
Dim currentcharind As Long
Dim nextcharind As Long

str1Len = Len(txtstring1)
tempstr = ""
ctr = 0
currentcharind = 1
nextcharind = 0
For ctr = 1 To str1Len - 1
currentcharind = Asc(Mid(txtstring1, ctr, 1))
nextcharind = Asc(Mid(txtstring1, ctr + 1, 1))

If currentcharind <= nextcharind Then
tempstr = tempstr & Mid(txtstring1, ctr, 1) & Mid(txtstring1, ctr + 1, 1)
Else
tempstr = tempstr & Mid(txtstring1, ctr + 1, 1) & Mid(txtstring1, ctr, 1)
ctr = ctr + 1
End If
Next

sortwdaz = tempstr

End Function

Any advice etc would be greatly appreciated!

ajetrumpet · Jan 7, 2008

Mousie,

What exactly does your function do? Is it close? What does it return? I think something like this is incredibly tough to figure out, but hey, that's what makes it fun! When I read through this, I thought about how you would do it. I only got as far as assigning Array elements to the counts that apply to the letter being evaluated. I say "count" as referring to the number of times the evaluated letter is less than any other letter in the rest of the string. I did that portion like this:

Code:

dim mystring As String
  dim int1 As Integer 
    dim int2 As Integer
      dim str1 As String
        dim str2 As String
          dim intcounter As Integer

For int1 = 1 To Len(mystring)

intcounter = 0
  
  str1 = Mid(mystring, int1, 1)
      
    For int2 = 1 To Len(mystring)
      str2 = Mid(mystring, int2, 1)
          
        If str1 < str2 Then
          intcounter = intcounter + 1
        End If
      
    Next int2
      
Next int1

After the "int2" LOOP, maybe you could fill the array value (IntCounter) with the letter being evaluated (as the Element - which is "str1"). But, before you start looping, you would have to declare the array with the number of elements needed (myarray = Array(len(mystring)).

The only problem with the way I am doing this is that it only works for strings that don't have repeated letters in them (which is probably not useful enough). But, modifying it to comply shouldn't be that difficult once you've mastered the process you want to use.

The other bad thing about this, is that if it did work, the highest array element value would be associated with the lowest letter of the alphabet. So, the process of getting the values back would involve LOOPING through the elements backwards. What do you think of this idea? Am I talking crazy here?

mousie · Jan 7, 2008

Hi ajetrumpet , I'm looking to build a "Srabble helper" that will return a list of words based on a user's input from they're letter's sorted a-z

My current macro does the first "Bubble" while re-arranging the middle and last letters, but obviously needs a few parses before it's complete. Also it's introducing some garbage characters in some runs.

here are my current results

inputWord Expr1
AKEE AKEK
AKEES AKEKES
AKELA AKEKAL
AKELAS AKEKAL
AKENE AKEKEN
AKENES AKEKEN
AKIMBO AKIKBM

My brain is a little fried at the moment, but I haven't been able to close your vba off. I think possibly looping backwards would be the safer option!

ajetrumpet · Jan 7, 2008

mousie said:
Hi ajetrumpet , I'm looking to build a "Srabble helper" that will return a list of words based on a user's input from they're letter's sorted a-z

Well, as I said before, I think the sorting function is, by itself, incredibly difficult to write (although it is possible)! I guess if you get the letters sorted, then you can carry out the rest of the process to get what you want?

Anyway, if you want a simple breakdown of the steps I think you have to go through, here they are:

1) Loop through every letter and assign an integer to it, based on the number of other letters in the string that are greater than the loop letter.
2) Put each letter into the array element that corresponds to the integer that was assigned.
3) After all the letters are evaluated, pull the elements out backwards, starting with the highest element value. Concat the element values in order (from highest to lowest).

To tell you the truth Mousie, I don't even know if this is possible, but just by reading what I wrote, I say it makes sense. But, accomplishing it is a completely different feat.

Cosmos75 · Jan 7, 2008

The sort code is based on code from a KB article - How To Sort Algorithms for Numeric Arrays. Not sure if there are any issues with using it for strings, so be sure to test it first.

Code:

Option Compare Database
Option Explicit

Public Function SortString(strString As String) As String

    Dim l As Long, lngLen As Long, s() As Variant
    lngLen = Len(strString)
    ReDim s(1 To lngLen) As Variant

    For l = 1 To lngLen
        s(l) = Mid(strString, l, 1)
    Next l
    
    BubbleSortString s
    
    For l = 1 To lngLen
        SortString = SortString & s(l)
    Next l
    
    Debug.Print "SortString('" & strString & "') = " & SortString
    
End Function

Public Function BubbleSortString(ByRef iArray As Variant)

    'Based on http://support.microsoft.com/kb/169617/en-us
    
    Dim lLoop1 As Long
    Dim lLoop2 As Long
    Dim lTemp As String

    For lLoop1 = UBound(iArray) To LBound(iArray) Step -1
        For lLoop2 = LBound(iArray) + 1 To lLoop1
            If iArray(lLoop2 - 1) > iArray(lLoop2) Then
                lTemp = iArray(lLoop2 - 1)
                iArray(lLoop2 - 1) = iArray(lLoop2)
                iArray(lLoop2) = lTemp
            End If
        Next lLoop2
    Next lLoop1


    For lLoop1 = LBound(iArray) To UBound(iArray)
        Debug.Print "iArray(" & lLoop1 & ") = " & iArray(lLoop1)
    Next lLoop1


End Function

SortString("cba") = abc

mousie · Jan 8, 2008

Excellent, thanks Ajetrumpet & Cosmos, The quoted KB article & code worked in my scenario

Thanks!!!!

Cosmos75 · Jan 8, 2008

mousie,

Glad that worked for you.

Be sure you remove the following code lines.

From SortString()

Code:

    Debug.Print "SortString('" & strString & "') = " & SortString

and from BubbleSortString()

Code:

    For lLoop1 = LBound(iArray) To UBound(iArray)
        Debug.Print "iArray(" & lLoop1 & ") = " & iArray(lLoop1)
    Next lLoop1

I put that there to help people see what the results are.

ajetrumpet · Jan 8, 2008

Cosmos,

You should really put that SortString() function in the code repository. That's awesome.

DALeffler · Jan 9, 2008

Try this:

Code:

Function fncSortStr(strPassed As String) As String
Dim Temp As String
Dim I As Integer

While Len(strPassed)
    Temp = Left(strPassed, 1)
    
        For I = 2 To Len(strPassed)
            If Mid(strPassed, I, 1) < Temp Then
                Temp = Mid(strPassed, I, 1)
            End If
        Next I
    
    fncSortStr = fncSortStr & Temp
    strPassed = Left(strPassed, InStr(1, strPassed, Temp) - 1) & _
                        Mid(strPassed, InStr(1, strPassed, Temp) + 1)
Wend

End Function

Cosmos75 · Jan 10, 2008

ajetrumpet said:
You should really put that SortString() function in the code repository. That's awesome.

Thanks for the vote of confidence but I remembered that a Shell sort is faster than a Bubble sort, so I was tweaking the code to post a newer version but I ran into a problem.

It sorts out that sorting characters is more complicated that I initialy thought. See this article -
A Better Shell Sort: Part I

Try using the sorting function to sort "aAAabBBb"; the result I get is "aAAabBBb". What I would ideally like to see if either "aaAAbbBB" or "AAaaBBbb". I figured out how to do with help from the article mentioned above, but it gets even more maddening when special characters are involved. If I manage to figure it out, I'll post back a new version. Or if someone else already knows how to do it, I'd love to see how it's done.

ajetrumpet · Jan 10, 2008

Cosmos,

If you enter "Bob", you get oBb. If you enter "boB", you get obB. So it is only sorting by shell, and not evening taking into account, the "Bubble". Did you figure out how to do this? The only reason you got the same input value back for "aAAabBBb" is because it is already Shell Sorted as written. This is getting into some knowledge that is way past what I can do I think...

DALeffler · Jan 10, 2008

Code:

Function fncSortStr(strPassed As String) As String
Dim Temp As String
Dim iTemp As Integer
Dim I As Integer

While Len(strPassed)
    iTemp = 1
    Temp = Left(strPassed, 1)
    
        For I = 2 To Len(strPassed)
        If StrComp(Mid(strPassed, I, 1), Temp, vbTextCompare) = 0 Then
            If StrComp(Mid(strPassed, I, 1), Temp, vbBinaryCompare) = -1 Then
                Temp = Mid(strPassed, I, 1)
                iTemp = I
            End If
        End If

        If StrComp(Mid(strPassed, I, 1), Temp, vbTextCompare) = -1 Then
                Temp = Mid(strPassed, I, 1)
                iTemp = I
            End If
        Next I
    
    fncSortStr = fncSortStr & Temp
    strPassed = Left(strPassed, iTemp - 1) & _
                        Mid(strPassed, iTemp + 1)
Wend

End Function

?fncsortstr("aBabAb")
returns: AaaBbb

If you change

Code:

If StrComp(Mid(strPassed, I, 1), Temp, vbTextCompare) = 0 Then
     If StrComp(Mid(strPassed, I, 1), Temp, vbBinaryCompare) = -1 Then

to

Code:

If StrComp(Mid(strPassed, I, 1), Temp, vbTextCompare) = 0 Then
     If StrComp(Mid(strPassed, I, 1), Temp, vbBinaryCompare) = 1 Then

?fncsortstr("aBabAb")
Returns: aaAbbB

ajetrumpet · Jan 10, 2008

Thanks Doug. Very nice!

Cosmos75 · Jan 10, 2008

Try these strings

Test String, Result
1) aBabAb, aaAbbB
2) aaAaAaaAA, aaaaaAAAA
3) aaAabAaaAA, aaaaaAAAAb
4) aaAaBAaaAA, BaaaaaAAAA
5) aBCabcAb, BaaAbcCb
6) aAAabBBbAaaA, aaAAAAbBBaab
7) ABCDEFGHIJKLMNOPQRSTUVWXYZijklmnopqrstuvwxyz, ABCDEFGHIJKLMNOPQRSTUVWXYZijklmnopqrstuvwxyz

If gets worse when you have a string with special characters; i.e. NOT 0-9, a-z, or A-Z.

Here are the ASCII characters (excluding the non-printing characters like carraige return) sorted in order of the character/HEX value.

!"#$%&'()*+,-./012...789:;<=>?@ABC...XYZ[\]^_`abc...xyz{|}~ ¡¢£¤¥¦§¨©ª«¬*®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿœŒšŠŸžŽƒˆ˜–—‘’‚“”„†‡•…‰‹›€™'

I think what most of us are looking for is this: -
Internal (English/American) Sort Routine Order

!"#...:;<...[\]...{|}... accented characters 0...9AaBbCc...XxYyZz

But StrComp sorts characters based on the ASCII order (depending on option selected, see below). So when comparing "a" to "A" it works fine, "a" to "b" is fine as is "A" to "b". BUT when comparing "B" to "a", it will put "B" before "a" since upper-case come before lower-case; see 3) & 4). Even then, since I don't truly understand the sorting algorithms, the results are not consistent; see 6). That's the point where I'm probably in over my head!

Here's what StrComp() returns and it means.
str1 < str2 = -1 'str1 comes BEFORE str2
str1 = str2 = 0 'SAME character
str1 > str2 = 1 'str1 comes AFTER str2

There are two comparison options you can use with StrComp, vbBinaryCompare and vbTextCompare.

Code:

StrComp("a", "A", vbBinaryCompare) = 1 'Aa
StrComp("B", "a", vbBinaryCompare) = -1 'Ba
StrComp("b", "B", vbBinaryCompare) = 1 'Bb

StrComp("a", "A", vbTextCompare) = 0 'aA or Aa
StrComp("B", "a", vbTextCompare) = 1 'aB
StrComp("b", "B", vbTextCompare) = 0 'bB or Bb

Using the vbBinaryCompare option, the sort is based on the ASCII order which may or may not suit your needs. The problem with using the vbTextCompare option, is that it ignores the character's case when comparing the same alphabet; e.g. "a" & "A".

Code:

StrComp(a, B, vbBinaryCompare) = 1 
StrComp(A, B, vbBinaryCompare) = -1 
StrComp(A, b, vbBinaryCompare) = -1 
StrComp(a, b, vbBinaryCompare) = -1

StrComp(a, B, vbTextCompare) = -1 
StrComp(A, B, vbTextCompare) = -1 
StrComp(A, b, vbTextCompare) = -1 
StrComp(a, b, vbTextCompare) = -1

The reason my original code failed is that is just did a logical comparison of the characters, which is not case-sensitive.
e.g.

Code:

("a" < b") = True
("a" < B") = True
("A" < B") = True
("A" < b") = True
...
("a" = A") = True
("A" = A") = True
("A" = a") = True

And to make things even more confusing, if you create a table with each character in a record and sort it by that character, once again it's different. I think it is 'Language Driver Order':

!"...?@[\]...0¼½¾¹12²³3..89AaªÁáàÀâÂÄä...æÆBbC...Ž

See this article for more info on those sort orders I mentioned; lstrcmpi, Accented Chars, and Sort Order in Windows.

ajetrumpet · Jan 11, 2008

Well,

this is out of the realm of my understanding now, so I'll shut up.

Sorting characters within a string

mousie

Registered User.

ajetrumpet

Banned

mousie

Registered User.

ajetrumpet

Banned

Cosmos75

Registered User.

mousie

Registered User.

Cosmos75

Registered User.

ajetrumpet

Banned

DALeffler

Registered Perpetrator

Cosmos75

Registered User.

ajetrumpet

Banned

DALeffler

Registered Perpetrator

ajetrumpet

Banned

Cosmos75

Registered User.

ajetrumpet

Banned

Similar threads

Users who are viewing this thread