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.