How to determine a string within a string includes all numbers

Question

I have a string that is suppose to be a word code. It includes two letters followed by a number(s).

I'm trying to determine which combinations of word code are all in that string. I'm separating out the letters by putting each word/numbers in a array but i'm stuck at how would i know if for example 16-28 means all numbers between 16 through 28.

Here's my code :

Dim arrayString() As String
arrayString = Split("MH12, MH13, MH16-28").Value, ",")
For i = 0 To UBound(arrayString)
    msgString = arrayString(i) & vbCr
Next i

Output I need to be able to determine should be :

MH12, MH13, MH16, MH17, MH18, MH19, MH20, MH21, MH22, MH23, MH24, MH25, MH26, MH27, MH28

Answer 1

Here is a function which you might find useful:

Function ExpandString(str As String, Optional delim As String = ",") As String
    Dim terms As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    Dim term As String, result As String, numstr As String

    terms = Split(str, delim)
    For i = 0 To UBound(terms)
        term = Trim(terms(i))
        If Not term Like "*-*" Then
            result = result & delim & term
        Else
            numstr = Mid(term, 3)
            m = Split(numstr, "-")(0)
            n = Split(numstr, "-")(1)
            For j = m To n
                result = result & delim & Left(term, 2) & j
            Next j
        End If
    Next i
    If Len(result) >= 2 Then result = Mid(result, 1 + Len(delim))
    ExpandString = result
End Function

For example, in the Immediate Window:

?ExpandString("MH12, MH13, MH16-28")
MH12,MH13,MH16,MH17,MH18,MH19,MH20,MH21,MH22,MH23,MH24,MH25,MH26,MH27,MH28

And also (note the space after the comma):

?expandstring("MH12, MH13, MH16-28", ", ")
MH12, MH13, MH16, MH17, MH18, MH19, MH20, MH21, MH22, MH23, MH24, MH25, MH26, MH27, MH28