Splitting a string cleanly at the end of words

Splitting a string cleanly at the end of words

Often, if you have a long string, you might want to split it into shorter lengths, so that it better fits the control space. However, if you just split it into 20 character lengths, chances are that you will end up splitting words in half, which does not aid readability. Instead, you can use something like this procedure to split the string at the end of the first word ending that appears after the specified number of characters:

Function fSplit(strIn As String, intCharacters As Integer) As String
'   Procedure to split a string tidily, without cutting words in half
'   Accepts:
'       strIn - the string that is to be split
'       intCharacters - the number of characters to split the string at
'   Returns:
'       a string that has been split correctly
    On Error GoTo E_Handle
    Dim strOut As String, strDummy As String
    Dim intLoop As Integer, intLen As Integer
    strDummy = Trim(strIn)
    intLen = Len(strDummy)
    If intLen <= intCharacters Then
        strOut = strDummy
    Else
        If InStr(strDummy, " ") > 0 Then
            Do While Len(strDummy) >= intCharacters
                strOut = strOut & Left(strDummy, intCharacters)
                strDummy = Mid(strDummy, intCharacters + 1)
                If InStr(strDummy, " ") = 0 Then
                    strOut = strOut & strDummy
                    strDummy = ""
                Else
                    strOut = strOut & Left(strDummy, InStr(strDummy, " ") - 1) & vbCrLf
                    strDummy = Trim(Mid(strDummy, Len(Left(strDummy, InStr(strDummy, " ") - 1)) + 1))
                End If
            Loop
        Else
            strOut = strDummy
        End If
    End If
    fSplit = strOut
fExit:
    On Error Resume Next
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

This can then be used as below (in the immediate window):

?fSplitString("This is a tester of the splitting function",8)
This is a
tester of
the splitting
function

Top

Last modified at 06/06/2006 14:57:18