Handling series of comma separated values in VBA

Question

I have this function which returns as string the value in comma separated string which is in order of given integer value.

Private Sub TestGetNthNumber()
    Debug.Print GetNthNumber("NUMBERS 5088, 5089, 5090, 5091", 2)
End Sub

Public Function GetNthNumber(sMark As String, iOrder As Integer) As String
    Dim sTemp As String
    Dim sNumber As String
    Dim iLoop As Integer

    If sMark = "" Then
        Exit Function
    End If

    sTemp = sMark & ","

    For iLoop = 1 To iOrder
        sTemp = Mid(sTemp, InStr(sTemp, " "))
        sNumber = Trim(Left(sTemp, InStr(sTemp, ",") - 1))
        sTemp = Mid(sTemp, InStr(sTemp, ",") + 1)
    Next

    GetNthNumber = sNumber
End Function

The test Sub will return "5089" as its given number 2 in the list of values.

My question; Is there better method to do this instead of the messy string manipulation with Mid, Left and InStr? Like a way to turn the comma separated string values into an array?

Another problem; It is possible the string is in format "NUMBERS 5088, 5089, 5090 and 5091". But for this I assume simply replacing " and" with "," before handling it does the trick.

score 2 · Accepted Answer · answered Jul 12 '13 at 07:03

2

The other alternative is to use RegEx/RegExp. Your function will looks like that:

Public Function GetNthNumberAlternative(sMark As String, iOrder As Integer) As String

    'regexp declaration
    Dim objRegExp As Object
    Set objRegExp = CreateObject("vbscript.regexp")

    With objRegExp
        .Global = True
        .Pattern = "\d+"

        GetNthNumberAlternative = .Execute(sMark)(iOrder - 1).Value 
    End With

End Function

And you could call it in this way:

Private Sub TestGetNthNumber()
    Debug.Print GetNthNumberAlternative("NUMBERS 5088 AND 5089 OR 5090, 5091", 1)
End Sub

answered Jul 12 '13 at 07:03

Kazimierz Jawor

18,861
7
35
55

That is simply awesome... I am "regex disabled" and should really learn to use regex's properly. They seem to give good solutions in many areas. So, "\d+" means "any number (digit)"? So it plain simple separates it into separate numbers found in string no matter what the text is? Thanks, this sure helps. – Raybarg Jul 12 '13 at 07:33
1

I'm quite excited with regex recently using it as much as possible ;) Yes, `\d+` will find any numbers within text. If you make your numbers more complex you will possible need to change pattern. I've found [this site](http://regex101.com/r/qV5jJ5) very helpful for test&trial when learning regex. – Kazimierz Jawor Jul 12 '13 at 07:37
That site seems great, very helpful. – Raybarg Jul 12 '13 at 08:47
This [post](http://stackoverflow.com/q/22542834/2521004) might help you understand Regex capabilities within Excel. – Automate This Mar 25 '14 at 03:42

score 1 · Answer 2 · answered Jul 12 '13 at 07:01

1

You want to use the Split function. If you have the same values each time then you can remove the NUMBERS and the final AND. Something like this:

Private Sub TestGetNthNumber()
    Debug.Print GetNthNumber("NUMBERS 5088, 5089, 5090, 5091", 2)
End Sub

Public Function GetNthNumber(sMark As String, iOrder As Integer) As String
    Dim vArray As Variant

    sMark = Replace(sMark, "NUMBERS", "")
    sMark = Replace(sMark, "AND", "")
    vArray = Split(sMark, ",")

    GetNthNumber = vArray(iOrder)

End Function

answered Jul 12 '13 at 07:01

CuberChase

4,458
5
33
52

Helpful answer, this made me learn about Split() function. Thank you. – Raybarg Jul 12 '13 at 08:50
Yeah no problems, for basic data it works very well. Regex is much more powerful of course depending on what you need. – CuberChase Jul 12 '13 at 10:29

Handling series of comma separated values in VBA

2 Answers2

Linked