Split string in VBA using RegEx - Not Splitting

Question

Really new at VBA/RegEx currently I have the RegEx function defined

Public Function splitLine(line As String) As String()

Dim regex As Object
Set regex = CreateObject("vbscript.regexp")
regex.IgnoreCase = True
regex.Global = True

'This pattern matches only commas outside quotes
'Pattern = ",(?=([^"]"[^"]")(?![^"]"))"
regex.Pattern = ",(?=([^" & Chr(34) & "]" & Chr(34) & "[^" & Chr(34) & "]" & Chr(34) & ")(?![^" & Chr(34) & "]" & Chr(34) & "))"


splitLine = Split(regex.Replace(line, ";"), ";")

End Function

And I reference that with:

Dim Resp As String: Resp = Http.ResponseText
Dim Lines As Variant: Lines = Split(Resp, vbLf)
Dim sLine As String
Dim Values As Variant

For i = 0 To UBound(Lines)
    sLine = Lines(i)
    Values = splitLine(sLine)

    Stop

Next i

This isn't curerntly throwing an error - there is just no split happening.

Thanks for any help!

score 0 · Answer 1 · edited May 23 '17 at 12:06

The local article on Regex to pick commas outside of quotes has some slightly different pattern strings which appear successful.

'Pattern = /^([^"]|"[^"]*")*?(,)/
regex.Pattern = "/^([^" &  Chr(34) & "]|" &  Chr(34) & "[^" &  Chr(34) & "]*" &  Chr(34) & ")*?(,)/"

'Pattern = /(,)(?=(?:[^"]|"[^"]*")*$)/
regex.Pattern = "/(,)(?=(?:[^" &  Chr(34) & "]|" &  Chr(34) & "[^" &  Chr(34) & "]*" &  Chr(34) & ")*$)/"

I would also suggest a less common delimiter to split on. ChrW(8203) (a zero-length unicode space) is a common delimiter for atoms and the like.

splitLine = Split(regex.Replace(line, ChrW(8203)), ChrW(8203))

I've culled together a working function using yet another regex pattern

Function stripCommasOutsideOfQuotedString(rng As Range)

    Dim strPattern As String
    Dim regEx As Object

    Set regEx = CreateObject("VBScript.RegExp")
    'pattern is: ,(?=([^"]*"[^"]*")*(?![^"]*"))
    strPattern = ",(?=([^" & Chr(34) & "]*" & Chr(34) & "[^" & Chr(34) & "]*" & Chr(34) & ")*(?![^" & Chr(34) & "]*" & Chr(34) & "))"
    'Debug.Print strPattern

    With regEx
        .Global = True
        .Pattern = strPattern
    End With

    stripCommasOutsideOfQuotedString = Split(regEx.Replace(rng.Value, ChrW(8203)), ChrW(8203))
End Function

The above function can be array-entered into a series of columns to receive the split values.

thanks for the advice! those patterns definitely look more comprehensive. unfortunately the split function still doesn't appear to be splitting. Values is still spitting out a Variant/String(0 to 0) leaving all the data as one string — wtsnk, Jul 09 '15 at 13:24

score 0 · Answer 2 · answered Jul 14 '15 at 13:43

I'm no programmer, by any means, but I have managed to piece together this little guy to pull down info from yahoo finance for stock information. Figured I'd leave this here:

Function stripCommasOutsideOfQuotedString(rng As String) As String()

Dim strPattern As String
Dim regEx As Object

Set regEx = CreateObject("VBScript.RegExp")
'pattern is: ,(?=([^"]*"[^"]*")*(?![^"]*"))
strPattern = ",(?=([^" & Chr(34) & "]*" & Chr(34) & "[^" & Chr(34) & "]*" & Chr(34) & ")*(?![^" & Chr(34) & "]*" & Chr(34) & "))"
'Debug.Print strPattern

With regEx
    .Global = True
    .Pattern = strPattern
End With

stripCommasOutsideOfQuotedString = Split(regEx.Replace(rng, ChrW(8203)), ChrW(8203))

End Function

Private Sub btnRefresh_Click()
  Dim W As Worksheet: Set W = ActiveSheet
  Dim Last As Integer: Last = W.Range("A1000").End(xlUp).Row
  If Last = 1 Then Exit Sub
  Dim Symbols As String
  Dim i As Integer
  For i = 2 To Last
    Symbols = Symbols & W.Range("A" & i).Value & "+"
  Next i
  Symbols = Left(Symbols, Len(Symbols) - 1)


  Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=npobat8mwva2j1rey"



  Dim Http As New WinHttpRequest
  Http.Open "GET", URL, False
  Http.Send

  Dim Resp As String: Resp = Http.ResponseText
  Dim Lines As Variant: Lines = Split(Resp, vbLf)
  Dim sLine As String
  Dim Values As Variant
  For i = 0 To UBound(Lines)
    sLine = Lines(i)
    If InStr(sLine, ",") > 0 Then

        Values = stripCommasOutsideOfQuotedString(sLine)
        W.Cells(i + 2, 2).Value = Replace(Values(0), Chr(34), "")
        W.Cells(i + 2, 3).Value = Replace(Values(1), Chr(34), "")
        W.Cells(i + 2, 4).Value = Replace(Values(2), Chr(34), "")
        W.Cells(i + 2, 5).Value = Replace(Values(3), Chr(34), "")
        W.Cells(i + 2, 6).Value = Replace(Values(4), Chr(34), "")
        W.Cells(i + 2, 7).Value = Replace(Values(5), Chr(34), "")
        W.Cells(i + 2, 8).Value = Replace(Values(6), Chr(34), "")
        W.Cells(i + 2, 9).Value = Replace(Values(7), Chr(34), "")
        W.Cells(i + 2, 10).Value = Replace(Values(8), Chr(34), "")
        W.Cells(i + 2, 11).Value = Replace(Values(9), Chr(34), "")
        W.Cells(i + 2, 12).Value = Replace(Values(10), Chr(34), "")
        W.Cells(i + 2, 13).Value = Replace(Values(11), Chr(34), "")
        W.Cells(i + 2, 14).Value = Replace(Values(12), Chr(34), "")
        W.Cells(i + 2, 15).Value = Replace(Values(13), Chr(34), "")
    End If
Next i
W.Cells.Columns.AutoFit

End Sub

Split string in VBA using RegEx - Not Splitting

2 Answers2