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:
- oString: a string which needs to be taken to split upto a separator
- oBrokenLength: refers to length upto each separator needs to be placed in given string
- 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
