Results 1 to 3 of 3
  1. #1
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    ESplit() Function

    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!
    Last edited by ajetrumpet; 09-10-2010 at 01:13 PM.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Good function. very helpful.

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    .
    nice.
    .
    .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. if function
    By lolo in forum Queries
    Replies: 1
    Last Post: 08-01-2010, 11:38 PM
  2. Want function to get current function name
    By Davis DeBard in forum Programming
    Replies: 2
    Last Post: 08-13-2009, 05:02 AM
  3. Sum Function Help
    By newbie in forum Reports
    Replies: 3
    Last Post: 06-30-2009, 05:32 PM
  4. Avg Function
    By hiker8117 in forum Access
    Replies: 3
    Last Post: 04-23-2009, 11:14 PM
  5. Is there a function to do this....
    By Nowherefast in forum Access
    Replies: 2
    Last Post: 12-31-2008, 08:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums