Split Text On Multiple Delimiters

This page describes two functions that are logical extensions of the VBA SPLIT function to allow splitting a character string using multiple delimiter characters and/or multiple-character delimiters.

Introduction

With VBA Version 6 (Office 2000), Microsoft introduced the Split function, which splits a text string into an array, using a delimiter character or characters to indicate the separation between words in the input string. For example, Split("ab|cd","|") returns a two element array, the first element being ab and the second being cd. The | character indidates the character that separates the strings. While Split does support mutliple character separator strings (e.g., Split("ab|$cd","|$") would return elements ab and cd), using the character sequence |$ as the delimiter string, it does not support multiple delimiters. For example, you cannot use Split to split apart the string ab|cd$ef%gh where |, $, and % are all considered text delimiters. This page describes two functions that expand on the VBA Split: SplitMultiDelims that allows you to specify any number of single-character delimiters, and SplitMultiDelimsEX which allows you to specify any number of single- or multiple-character delimiters. For example, you can split the string "yesterdaySTOPtodayENDtomrrowNEXTfuture" into the array {yesterday, today, tomorrow, future} by specifying STOP, END, and NEXT as the delimiter words. Of course, the delimiters need not be actual words; they may be any series of strings of any characters.

You can download an example bas module here.

The SplitMultiDelims Function

The SplitMultiDelims function is used to split a string into substrings using any of the supplied characters as delimiters between the substrings. Substrings are separated by any one character passed in the DelimChars characters. All delimiter characters are treated as single characters and no one character has any higher priority than any other character. That is, the order in which the character appear in the DelimChars string is irrelevant. The function returns an array of substrings parsed out from the Text parameter using the characters in the DelimChars parameter. If Text is empty, the function returns an unallocated array. If DelimChars is empty, the entire Text value is returned as a single element array.

SplitMultiDelimsEX Examples

If Text contains a|bc$def:ghij, and DelimChars contains the string :|$, the result is the array {a, bc, def, ghij}.
If Text does not contain any of the characters in DelimChars, the function returns a single element array containing all of Text.

The SplitMultiDelimsEX Code

Function SplitMultiDelims(Text As String, DelimChars As String) As String()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SplitMutliChar
' This function splits Text into an array of substrings, each substring
' delimited by any character in DelimChars. Only a single character
' may be a delimiter between two substrings, but DelimChars may
' contain any number of delimiter characters. If you need multiple
' character delimiters, use the SplitMultiDelimsEX function. It returns
' an unallocated array it Text is empty, a single element array
' containing all of text if DelimChars is empty, or a 1 or greater
' element array if the Text is successfully split into substrings.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos1 As Long
Dim N As Long
Dim M As Long
Dim Arr() As String
Dim I As Long

''''''''''''''''''''''''''''''''
' if Text is empty, get out
''''''''''''''''''''''''''''''''
If Len(Text) = 0 Then
    Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''
' if DelimChars is empty, return original text
'''''''''''''''''''''''''''''''''''''''''''''
If DelimChars = vbNullString Then
    SplitMultiDelims = Array(Text)
    Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' oversize the array, we'll shrink it later so
' we don't need to use Redim Preserve
'''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(1 To Len(Text))

I = 0
N = 0
Pos1 = 1

For N = 1 To Len(Text)
    For M = 1 To Len(DelimChars)
        If StrComp(Mid(Text, N, 1), Mid(DelimChars, M, 1), vbTextCompare) = 0 Then
            I = I + 1
            Arr(I) = Mid(Text, Pos1, N - Pos1)
            Pos1 = N + 1
            N = N + 1
        End If
    Next M
Next N

If Pos1 <= Len(Text) Then
    I = I + 1
    Arr(I) = Mid(Text, Pos1)
End If

''''''''''''''''''''''''''''''''''''''
' chop off unused array elements
''''''''''''''''''''''''''''''''''''''
ReDim Preserve Arr(1 To I)
SplitMultiDelims = Arr
    
End Function

The SplitMultiDelimsEX Function

The function SplitMultiDelimsEX function is an extension of the SplitMultiDelims function. Instead of being restricted to single-character delimiters, it allows for any number of multi-character string delimiters. These delimiters are passed in the DelimStrings parameter, and each delimiter string in this parameter is separated by the DelimStringsSep character. The strings in DelimStrings must not use the DelimStringsSep except as the string separator. The function returns an unallocated array if Text is empty, the full contents of Text as a single element array if DelimStrings is empty, or an array of 1 to many elements each of which is a substring of Text, separated by any string in the DelimStrings. The elements of DelimStrings are treated equally. That is, the order of the strings in DelimStrings is irrelevant.

The SplitMultiDelimsEX Code

Function SplitMultiDelimsEX(Text As String, DelimStrings As String, _
    DelimStringsSep As String) As String()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SplitMultiDelimsEX
' This function is like VBA's Split function or the SplitMultiDelims
' function, also in this module. It accepts any number of multiple-
' character delimiter strings and splits Text into substrings based
' on the delimiter strings. It returns an unallocated array if Text
' is empty, a single-element array if DelimStrings is empty, or a
' 1 or greater element array if Text was successfully split into
' substrings based on the DelimStrings delimiters.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos1 As Long
Dim N As Long
Dim M As Long
Dim Arr() As String
Dim I As Long
Dim DelimWords() As String
Dim DelimNdx As Long
Dim DelimWord As String

'''''''''''''''''''''''''''''
' if Text is empty, get out
'''''''''''''''''''''''''''''
If Len(Text) = 0 Then
    Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''
' if there are no delimiters, return the whole text
'''''''''''''''''''''''''''''''''''''''''''''''''''
If DelimStrings = vbNullString Then
    SplitMultiDelimsEX = Array(Text)
    Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''
' if there is no delim separator, get out
''''''''''''''''''''''''''''''''''''''''''
If DelimStringsSep = vbNullString Then
    Exit Function
End If

DelimWords = Split(DelimStrings, DelimStringsSep)
If IsArrayAllocated(DelimWords) = False Then
    Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''''''''
' oversize the array, we'll shrink it later so
' we don't need to use Redim Preserve
''''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(1 To Len(Text))

I = 0
N = 0
Pos1 = 1

For N = Pos1 To Len(Text)
    For DelimNdx = LBound(DelimWords) To UBound(DelimWords)
        DelimWord = DelimWords(DelimNdx)
        If StrComp(Mid(Text, N, Len(DelimWord)), DelimWord, vbBinaryCompare) = 0 Then
            I = I + 1
            Arr(I) = Mid(Text, Pos1, N - Pos1)
            Pos1 = N + Len(DelimWord)
            N = Pos1
        End If
    Next DelimNdx
Next N

If Pos1 <= Len(Text) Then
    I = I + 1
    Arr(I) = Mid(Text, Pos1)
End If

''''''''''''''''''''''''''''''''''''
' chop off unused array elements
''''''''''''''''''''''''''''''''''''
ReDim Preserve Arr(1 To I)
SplitMultiDelimsEX = Arr
    
End Function

You can download an example bas module here.

This page last updated: 20-September-2007

Created by Chip Pearson at Pearson Software Consulting, LLC
Email: chip@cpearson.com Before emailing me, please read this page. 
http://cpearson.com/excel/splitondelimiters.aspx
Copyright © 1997 - 2007, Charles H. Pearson



About This Web Site Activate The Main Excel Window Adding Dates Adding The COM Add-Ins Menu Item Adding the COM Add-Ins menu item Alerter Display Library Alerter Message Display Component Analysis ToolPak Application Events Array Formulas Array Formulas Arrays In VBA Arrays To Columns Attachments In Newsgroups Automation Add-Ins Blinking Text Blinking Text Bracket Pricing Browse Folder CALL Function Call Stack Calling Worksheet Functions Cell Referencing Cell View Add-In Class Modules Clipboard Functions Clone A Folder Code Modules Code Notes Collections And Dictionaries Color Band Formatting Colors And Functions COM Add-Ins In VB6 Conditional Formatting Conditional Formatting Colors Connected And Disconnected Objects Consulting And Development Services Creating An Add-In Date And Time Quick Entry Date And Time VBA Functions Date And Time Worksheet Functions Date And Times In Excel Date Arithmetic Date Intervals DATEDIF Function Dates And Times Daylight Savings Time Debugging VBA Declaring Variables Default Property Of A Class Deleting Duplicates Deleting Duplicates With Filter Detecting Scrolling Direct Connect The MSNEWS Directory Tree Listings Distributed Dates Document Properties Downloadable Files Downloadable Zip Files Downloads Duplicate Data In Lists And Ranges Duplicates In Lists Email Me Emailing ME Empty A Folder Enum Data Types Error Handling In VBA Error Values And The FormatMessage API EULA Answers Event Procedures Events In UserForms Excel Macros Excel Pages Excel Shutdown Excel Tools Excel Worksheet Functions Feet And Inches File Name Of COM Add-In File Times Find In Range First And Last Names Folder Tree Listing Folder Tree Listing Form Positioner Formula Bar Shortcut Fractional Arithmetic Functions And Subs Games Get And Set Properties GetInfo Function Headers And Footers Hidden Namespace Holidays Import And Export Text Importing Large Files To Excel Importing Large Text Files INDIRECT Function Insert And Fill A Series Insert Procedure Names In VBA Intro To VBA Julian Dates Keyboard Shortcuts Keyboard Tests Last Time Of File Latitude And Longitude Legalese Legalese and Copyright Information Links List Lookups ListBox Utlities Lists Of Data In Excel Lists On Worksheets Local And GMT Times Main Page Main Page Make Mulitple Levels Of Directories Menu Items Menus In The VBA Editor Modifying The Registry Multi File Import In VBA Multiple Monitors And Windows Name Box Shortcut Named Ranges Nested Functions New Posters Guide For Newsgroups No Blanks No Dupicate Entries Office Application Window Classes Optimize Your VBA Code Ordinal Number Suffixes Overtime And Timesheets Parsing Phone Numbers Passing And Returning Arrays In VBA Passwords Pivot Tables Popup Box Privacy Statement Progress Bar Random Numbers Ranking Data Recursive Programming Recycle A File Or Folder References Registry Related Pages Relative And Absolute References Relative And Absolute References Resources For Developers Returning Arrays Rounding In Excel Rowliner Add-In Save Copy And Zip A Workbook Save Coy And Zip Add-In Scheduling Procedures With OnTime Search The Site Search The Site Series Information SetParent And Userforms Sheet Referencing Shell And Wait Shortcut Keys Shortcut Keys For VBA Show Any Form Site Feedback Size String Some Friendly Sort And Array In VBA Sort By Color Sort Worksheet Tabs Sorting Arrays Of Objects Sorting Objects Comparison Special Characters Special User Folders Startup Errors In Excel Status Bar In Excel Subclassing Windows Support Suppress Changes In Forms System Error Messages From Numbers Temporary Files And Folders Test Friendly Text Box Validation Time Zones Timed Close Topic Index Truly Global Variables Unselect A Cell Or Area Upper And Lower Case User Defined Functions User Defined Functions Validate A Text Box Values And Formulas Variables In VBA VBA Code To Modify VBA Code Versions Of Excel Wait For File Close Week Numbers What's New On The Site Widen The Name Box Word Count Of Worksheet Zomming And Centering  
0 - Rate This Page 1 - Not Helpful 2 - Somewhat Helpful 3 - Quite Helpful 4 - Very Helpful 5 - Exactly What I Needed


Essential Tools For Developers


Ready

Advertise Your Product On This Site