Microsoft Access: VBA Programming Code

Provided by Allen Browne, June 2006

ParseWord() function

This function parses a word or item from a field or expression.

It is similar to the built-in Split() function, but extends its functionality to handle nulls, errors, finding the last item, removing leading or doubled spacing, and so on.

It is particularly useful for importing data where expressions need to be split into different fields.

Use your own error logger, or copy the one in this link: LogError()

Examples

1. To get the second word from "My dog has fleas":
       ParseWord("My dog has fleas", 2)
2. To get the last word from the FullName field:
       ParseWord([FullName], -1)
3. To get the second item from a list separated by semicolons:
       ParseWord("first;second;third;fourth;fifth", 2, ";")
4. To get the fourth sentence from the Notes field:
       ParseWord([Notes], 4, ".")
5. To get the third word from the Address field, ignoring any doubled up spaces in the field:
       ParseWord([Address], 3, ,True, True)

Arguments

1. varPhrase: the field or expression that contains the word you want.
2. iWordNum: which word: 1 for the first word, 2 for the second, etc. Use -1 to get the last word, -2 for the second last, ...
3. strDelimiter: the character that separates the words. Assumed to be a space unless you specify otherwise.
4. bRemoveLeavingDelimiters: If True, any leading spaces are removed from the phrase before processing. Defaults to False.
5. bIgnoreDoubleDelimiters: If True, any double-spaces inside the phrase are treated as a single space. Defaults to False.

Return

The word from the string if found. Null for other cases, including the second word in this string, "Two  spaces", unless the last argument is True.

The code

Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer, Optional strDelimiter As String = " ", _
    Optional bRemoveLeadingDelimiters As Boolean, Optional bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler
    'Purpose:   Return the iWordNum-th word from a phrase.
    'Return:    The word, or Null if not found.
    'Arguments: varPhrase = the phrase to search.
    '           iWordNum = 1 for first word, 2 for second, ...
    '               Negative values for words form the right: -1 = last word; -2 = second last word, ...
    '               (Entire phrase returned if iWordNum is zero.)
    '           strDelimiter = the separator between words. Defaults to a space.
    '           bRemoveLeadingDelimiters: If True, leading delimiters are stripped.
    '               Otherwise the first word is returned as null.
    '           bIgnoreDoubleDelimiters: If true, double-spaces are treated as one space.
    '               Otherwise the word between spaces is returned as null.
    'Author:    Allen Browne. http://allenbrowne.com. June 2006.
    Dim varArray As Variant     'The phrase is parsed into a variant array.
    Dim strPhrase As String     'varPhrase converted to a string.
    Dim strResult As String     'The result to be returned.
    Dim lngLen As Long          'Length of the string.
    Dim lngLenDelimiter As Long 'Length of the delimiter.
    Dim bCancel As Boolean      'Flag to cancel this operation.

    '*************************************
    'Validate the arguments
    '*************************************
    'Cancel if the phrase (a variant) is error, null, or a zero-length string.
    If IsError(varPhrase) Then
        bCancel = True
    Else
        strPhrase = Nz(varPhrase, vbNullString)
        If strPhrase = vbNullString Then
            bCancel = True
        End If
    End If
    'If word number is zero, return the whole thing and quit processing.
    If iWordNum = 0 And Not bCancel Then
        strResult = strPhrase
        bCancel = True
    End If
    'Delimiter cannot be zero-length.
    If Not bCancel Then
        lngLenDelimiter = Len(strDelimiter)
        If lngLenDelimiter = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Process the string
    '*************************************
    If Not bCancel Then
        strPhrase = varPhrase
        'Remove leading delimiters?
        If bRemoveLeadingDelimiters Then
            strPhrase = Nz(varPhrase, vbNullString)
            Do While Left$(strPhrase, lngLenDelimiter) = strDelimiter
                strPhrase = Mid(strPhrase, lngLenDelimiter + 1&)
            Loop
        End If
        'Ignore doubled-up delimiters?
        If bIgnoreDoubleDelimiters Then
            Do
                lngLen = Len(strPhrase)
                strPhrase = Replace(strPhrase, strDelimiter & strDelimiter, strDelimiter)
            Loop Until Len(strPhrase) = lngLen
        End If
        'Cancel if there's no phrase left to work with
        If Len(strPhrase) = 0& Then
            bCancel = True
        End If
    End If

    '*************************************
    'Parse the word from the string.
    '*************************************
    If Not bCancel Then
        varArray = Split(strPhrase, strDelimiter)
        If UBound(varArray) >= 0 Then
            If iWordNum > 0 Then        'Positive: count words from the left.
                iWordNum = iWordNum - 1         'Adjust for zero-based array.
                If iWordNum <= UBound(varArray) Then
                    strResult = varArray(iWordNum)
                End If
            Else                        'Negative: count words from the right.
                iWordNum = UBound(varArray) + iWordNum + 1
                If iWordNum >= 0 Then
                    strResult = varArray(iWordNum)
                End If
            End If
        End If
    End If

    '*************************************
    'Return the result, or a null if it is a zero-length string.
    '*************************************
    If strResult <> vbNullString Then
        ParseWord = strResult
    Else
        ParseWord = Null
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, "ParseWord()")
    Resume Exit_Handler
End Function

How it works

The function accepts a Variant as the phrase, so you can use it where a field could be null (a field with no value) or error (e.g. trying to parse a field on a report that has no records.) The first stage is to validate the arguments before trying to use them.

The second stage is to pre-process the string to remove leading delimiters, or to ignore doubled-up delimiters within the string, if the optional arguments indicate the user wants this.

The Split() function parses the phrase into an array of words. Since the array is zero-based, the word number is adjusted by 1. If the word number is negative, we count down from the upper bound of the array. Note that iWordNum is passed ByVal since we are changing its value within the procedure.

Finally we return the result string, or Null if the result is a zero-length string.