Dynamic Split any String up-to given Separator VBA code example

In this article we will try to write code to split a string up to given length and put required separator. Refer below function which has following arguments:

1. oString: a string which needs to be taken to split upto a separator
2. oBrokenLength: refers to length upto each separator needs to be placed in given string
3. oSeparator: a character the string has to be separated with and must be 1 char long else function will throw error

Code

Public Function SplitStringToSeprator(oString As String, oBrokenLength As Integer, Optional oSeparator As String = "-") As String
On Error GoTo errh
    SplitStringToSeprator = "Split Error"
    'Check Separator should not be more than 1 char
    If Len(oSeparator) > 1 Then
        SplitStringToSeprator = "Invalid separator"
        Exit Function
    End If
    Dim insrter As String
    If Len(oString) > oBrokenLength Then
        Dim i As Long
        Dim totalLength As Long
        totalLength = Len(oString)
        For i = 1 To totalLength
            Dim oValTrim As String
            If insrter <> Empty Then
                insrter = insrter & oSeparator
            End If
            If Len(oString) > oBrokenLength Then
                oValTrim = Left(oString, oBrokenLength)
                If insrter <> Empty Then
                    insrter = insrter & oValTrim
                Else
                    insrter = oValTrim
                End If
                oString = Replace(oString, oValTrim, "")
            Else
                insrter = insrter & oString
            End If
            i = i + oBrokenLength
        Next i
    End If
    SplitStringToSeprator = insrter
errh:
If Err.Number <> 0 Then
    Exit Function
End If
End Function

Implementation

Output

Share on Facebook

Tweet