Q:

regex parsing delimited string

I am having trouble with parsing my delimited string with the following regex (this regex also takes into account situation, when user is using quotes as a grouping character):
"[^"]*"|[^;]*

This works perfectly fine when there is no empty space between the delimiter, such as following:
31.12.2015;M234;94 841,00;C

**results:**
31.12.2015
M234
94 841,00
C

However it fails, when some of the 'columns' / values are empty such as following:
31.12.2015;M234;94 841,00 ;C;;;0000-0000-00;0000000

The problem is, that it does not return empty space between my delimiters as a result and simply skips to a new delimiter.
What do I need to change to fix this regex?
Here is the code I am using to loop through values
For Each Match In sRegex.Execute(sRow)
    If Match.Length > 0 Or bDelimiter = False Then
        Debug.Print Match.Value
        sHolder(UBound(sHolder)) = Match.Value
        ReDim Preserve sHolder(0 To UBound(sHolder) + 1)
        bDelimiter = True
    Else
        bDelimiter = False
        Debug.Print "delimiter"
    End If
Next Match

A:

I see the problem is identifying whether the empty string is actually an empty string before a ; after a valid item, or whether is an empty item itself.
I suggest changing the regex to "[^"]*"|([^;]*);? to capture all non-;s before an optional ; that will be consumed, and no empty space will no longer be available for the regex to match. Some more logic should be introduced though.
Here is an example code:
Sub ExecuteTest2()
Dim s As String
Dim sRegex As New regexp
Dim sHolder() As String
Dim strPattern As String
strPattern = """[^""]*""|([^;]*);?"
s = "31.12.2015;M234;94 841,00 ;C;;;0000-0000-00;0000000"
sRegex.Global = True
sRegex.MultiLine = False
sRegex.IgnoreCase = True
sRegex.pattern = strPattern
ReDim Preserve sHolder(0)
For Each match In sRegex.Execute(s)
    If match.SubMatches.Count > 0 Then
        Debug.Print match.SubMatches(0)
        sHolder(UBound(sHolder)) = match.SubMatches(0)
        ReDim Preserve sHolder(0 To UBound(sHolder) + 1)
    Else
        Debug.Print match.Value
        sHolder(UBound(sHolder)) = match.Value
        ReDim Preserve sHolder(0 To UBound(sHolder) + 1)
    End If
Next match
End Sub

