Do you ever get data that doesn't have a consistent delimiter? If you do, then you know that the Split() function won't work for parsing purposes. Well...I got tired of not having a solution, so I wrote one myself. I named it ESplit() in honor of Allen Browne's "E" functions:
This function takes an input string that has more than one possible delimiter and outputs a correctly parsed array of string values. pList is a list of possible delimiters located in the Input String (E.G. - ". , ; : - _ |"). pListDelimiter is the delimiter (separator) of the list you provide (E.G. - in this case it is " ")
Code:
Function ESplit(strInput As String, pList As String, pListDelimiter As String)
'******************************************************************************
' *
'Author: Adam Evanovich *
'Date: 9/9/2010 *
'Purpose: To condense strings that need to be parsed into substrings. *
' These strings have more than one possible delimiter, which *
' eliminates the usefulness of the Split() function. *
' This code replaces multiple delimiters with just one. *
' *
' *
'pList is a list of possible delimiters, like what I show below: *
' *
'Example ----> ", . | ; : -" *
' *
'pListDelimiter is the separator you use for your list, as seen below: *
' *
'Example ----> " " *
' *
'******************************************************************************
Dim CtrAry As Integer 'ELEMENT COUNTER
Dim pAryList() As String 'LIST OF POSSIBLE DELIMITERS
Dim rDelimiter As String 'REPLACEMENT DELIMITER
Dim AryOutput As Variant 'FINAL OUTPUT ARRAY
pAryList() = Split(pList, pListDelimiter) 'GET LIST INTO ARRAY
rDelimiter = pAryList(LBound(pAryList)) 'FIRST DELIM IN LIST OF DELIMS IS REPLACEMENT FOR OTHERS
'REPLACE BAD DELIMS WITH REPLACEMENT DELIM
For CtrAry = 1 To UBound(pAryList)
strInput = Replace(strInput, pAryList(CtrAry), rDelimiter)
Next CtrAry
AryOutput = Split(strInput, rDelimiter) 'FINAL STRING TO PARSE
'PRINT OUTPUT
For CtrAry = LBound(AryOutput) To UBound(AryOutput)
Debug.Print AryOutput(CtrAry)
Next CtrAry
End Function
Example
*Input String = "This|is;one:very.cool,function-to_work,with!"
*pList = ". , ; : - _ |"
*pListDelimiter = " "
With a little moderation to the output loop code...
Code:
For CtrAry = LBound(AryOutput) To UBound(AryOutput)
ESplit = AryOutput(CtrAry) & " "
Next CtrAry
ESplit = Rtrim(ESplit)
you can output horizontally instead of vertically:
OutPut
This is one very cool function to work with!