How to sort an array of strings containing numbers

Question

I'm having some trouble correctly sorting an array numerically based on strings that contain numbers:

So, the sort works for all strings up until I get into 2 digit numbers.

For example, if the array contains "Issue 2:" "Issue 5:" and "Issue 3:" it correctly sorts the strings to be 2, 3, 5.

Unfortunately, as soon as I get two digit numbers it no longer sorts correctly. So "Issue 10:" "Issue 8:" and "Issue 13:" will not sort.

I'm fairly certain it has to do with the fact that I'm trying to sort based on strings than on numeric values. Is there a way to have it correctly sort via strings? Or is there an "easy" way to change the string numbers into actual numerical values.

   'This creates a list of what we want to sort by.  
   'The string format will always be "Issue 1:" "Issue 3:" "Issue 2:" "Issue 11:"
   'Issue x:" etc.

    IssueListActual = CreateIssueListFromSection(sectionFind)

    'This creates a duplicate array to be sorted
    IssueListSorted = IssueListActual

    'Sorts the array as seen in below subroutine
    BubbleSort IssueListSorted

Sub BubbleSort(arr)

Dim strTemp As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
  For j = i + 1 To lngMax
    If arr(i) > arr(j) Then
      strTemp = arr(i)
      arr(i) = arr(j)
      arr(j) = strTemp
    End If
  Next j
Next i
End Sub

AnalystCave.com · Accepted Answer · 2015-05-26T16:40:57.063

Strings are sorted by their characters so strings "10", "1" and "8" will be sorted differently then numbers 10,1 and 8.

Just strip the array of the string "Issue:" and convert the values in the array before comparing them to Longs using the CLng function like this: CLng(arr(i)). Your code would look something like this (I didn't test it):

Sub BubbleSort(arr)
  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If GetNumber(arr(i)) > GetNumber(arr(j)) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
End Sub

Function GetNumber(str)
    Dim no As String
    no = CStr(str)
    no = Mid(no, InStr(no, " ") + 1, InStr(no, ":") - InStr(no, " ") - 1)
    GetNumber = CLng(no)
End Function

Sub Test()
   Dim arr(0 To 2) As String
   arr(0) = "Issue 13:"
   arr(1) = "Issue 12:"
   arr(2) = "Issue 5:"
   Call BubbleSort(arr)
End Sub

I seem to be getting a compile time error with this: "Expected: ) " — Arcus, May 26 '15 at 16:31
This is really slick. I really need to learn how to do this manipulating data types to do what I want them to do. Do you have any recommended literature on this matter? — Arcus, May 26 '15 at 17:20

score 1 · Answer 2 · edited May 23 '17 at 12:14

1

1) Add the Function "onlyDigits" from this post to your module How to find numbers from a string?

2) change the first line of the function to

  Function onlyDigits(s As String) As Integer

3) change the last line of the function to

 onlyDigits = CInt(retval)

4) then change your line

     If arr(i) > arr(j) Then

to

     If onlyDigits(arr(i)) > onlyDigits(arr(j)) Then

Done.

edited May 23 '17 at 12:14

Community

1
1

answered May 26 '15 at 16:30

it means your values in the array are no strings. Since you didnt supply the definition of your array, this wasnt obvious. So cast your variables to strings first and call the function like this: onlyDigits(CStr(arr(i))) – May 26 '15 at 19:35

How to sort an array of strings containing numbers

2 Answers2

Linked