Split Function That Ignores Delimiters Located Inside Quote Marks

**Rick Rothstein** · 09-15-2012, 06:25 AM

NOTE
-------
I have updated the code in this message on Sept. 21, 2012 to reflect an excellent observation by snb in the messages he posted to this thread. Thanks snb!

Earlier today, Rasm started a thread (VBA - Split command) where he asked for the ability to use the Split function, but have it not split text at delimiters inside quote marks. An example would be this comma-delimited text string...

One,Two,"Three,Four",Five

where the returned array (call it Arr for this example) should be this...

Arr(0) ==> One
Arr(1) ==> Two
Arr(2) ==> "Three,Four"
Arr(3) ==> Five

Of course, VBA's built-in Split function does not do this... it will split apart the text in Arr(2) blindly ignoring the quote marks. Now, while this question has only just been asked in this forum, it is any old problem having roots going way back. Many, many years ago back when I was volunteering answering questions on the old Compiled VB Newsgroups, I posted a function that did what Rasm has asked for. Below is that function, but to which I have added some additional functionality.

Code:

Function QuotedSplit(ByVal TextToSplit As String, _
                     Optional Delimiter As String = ",", _
                     Optional RemoveDuplicates As Boolean = False, _
                     Optional Compare As VbCompareMethod = vbBinaryCompare) As String()
  Dim X As Long, QuoteDelimited() As String, WorkingArray() As String
  QuoteDelimited = Split(TextToSplit, """")
  For X = 0 To UBound(QuoteDelimited) Step 2
    QuoteDelimited(X) = Replace$(QuoteDelimited(X), Delimiter, Chr$(1), , , Compare)
  Next
  TextToSplit = Join(QuoteDelimited, """")
  If RemoveDuplicates Then
    Do While InStr(1, TextToSplit, Chr(1) & Chr(1), Compare)
      TextToSplit = Replace$(TextToSplit, Chr(1) & Chr(1), Chr(1), , , Compare)
    Loop
  End If
  WorkingArray = Split(TextToSplit, Chr$(1))
  QuotedSplit = WorkingArray
End Function

The above function has four arguments... the first is the text that you want to split apart and is, of course, required; the second, the text you want to use as the delimiter is optional and is defaulted to a comma, but which can be any text string composed of one or more characters; the third is a Boolean which allows you to specify how consecutive delimiters are to be treated, where the default is False meaning the each delimited field will be returned even if that delimited field is the empty string ("") and where True will make the function treat consecutive delimiters as if they were a single delimiter; and the fourth is an optional built-in data type (VbCompareMethod - actually, a restricted Long underneath it all) which, if the delimiter has alphabetic characters in it, allows you to make the split case sensitive or not... the default is vbBinaryCompare (0) meaning the text must match the case of the delimiter you pass in exactly, set it to vbTextCompare (1) if you want the split to be performed with regard for the letter casing of the delimiter.

Oh, and I guess I should mention... this function, like the Split function which it is modeled after, returns a zero-based one-dimensional String array. Okay, that is it... try playing around with the various options so you can get a "feel" for what they do and how they interact with each other.

**snb** · 09-17-2012, 12:42 AM

Just to illustrate an approach:

Code:

Sub snb()
    c00 = "One,Two,""Three,Four"",Five"
    
    sp = Split(c00, Chr(34))
    For j = 0 To UBound(sp) Step 2
        sp(j) = Replace(sp(j), ",", "_")
    Next
    sn = Split(Join(sp, Chr(34)), "_")
    
    MsgBox Join(sn, vbLf)
End Sub

**Rick Rothstein** · 09-17-2012, 12:59 AM

Originally Posted by snb

Just to illustrate an approach:

Code:

Sub snb()
    c00 = "One,Two,""Three,Four"",Five"
    
    sp = Split(c00, Chr(34))
    For j = 0 To UBound(sp) Step 2
        sp(j) = Replace(sp(j), ",", "_")
    Next
    sn = Split(Join(sp, Chr(34)), "_")
    
    MsgBox Join(sn, vbLf)
End Sub

**snb** · 09-17-2012, 01:26 AM

I had no intention to present an alternative approach to all the functionality you provided.
As far as I can see the main difference is in which part to replace the delimiter: in your code you replace 'inside' the quotation marks; I do that 'outside' the quotation marks.
You use 2 different replacers: chr(0) & chr(1); I only use 1
That saves some additional replacements.

If you think my post confuses more that it enlightens, please feel completely free to remove it.

**Rick Rothstein** · 09-21-2012, 10:27 PM

Originally Posted by snb

I had no intention to present an alternative approach to all the functionality you provided.
As far as I can see the main difference is in which part to replace the delimiter: in your code you replace 'inside' the quotation marks; I do that 'outside' the quotation marks.
You use 2 different replacers: chr(0) & chr(1); I only use 1
That saves some additional replacements.

Sorry about the delay in getting back to you on this. The part I highlighted in red is an excellent observation... when I first developed the code (many years ago now), my thought was I had to actively protect the text inside the quotes, but as your postings indicate... I do not have to do it that way... rather, simply changing the non-quoted delimiters and then directly manipulating those replacements makes the code tighter and more efficient. Thank you for your incites on this... much appreciated. By the way, I have changed the code in my original posting to reflect the changes you suggested.