String length over 3 lines based on length

Question

So I'm trying to split a string via VBA in excel over 3 lines if it is over a certain length. I have it working for 2 lines but if it is over ~55 characters it overlaps onto other text in the ouput file. I want it dynamic so it copes with all cases i.e 10 to 60 characters , splitting up into lines accordingly. Any suggestions ?

 If Len(StrText) > 19 Then
           If Mid(StrText, 11, 1) = " " Then
              StrText = Left(StrText, 19) & Chr(10) & Chr(32) & Right(StrText, Len(StrText) - 19)
    Else
        m = 19
        Do
            m = m + 1
        Loop Until ((Mid(StrText, m, 1) = " ") Or (m = Len(StrText)))
        StrText = Left(StrText, m) & Chr(10) & Chr(32) & Right(StrText, Len(StrText) - m)
    End If
End If

Answer 1

Below UDF should be able to do that for you. Assumption here is that your string has spaces.. if it doesn't, UDF will most likely throw unexpected results (I haven't catered for that scenario)

Function SetString(ByVal sText As String) As String
    Dim sGreater55$, sGreater19$

    ' If text is greater than 55 characters, first lets capture the string after 55 characters (depending on where the SPACE character is)
    If Len(sText) > 55 Then
        If InStr(55, sText, " ") > 0 Then
            sGreater55 = Mid(sText, InStr(55, sText, " "))
        Else
            sGreater55 = Mid(sText, InStrRev(sText, " ", 55))
        End If
    End If

    ' If text is greater than 19 characters, lets build the string after 19 characters (depending on where the SPACE character is)
    If Len(sText) > 19 Then
        If InStr(19, sText, " ") > 0 Then
            sGreater19 = Mid(sText, InStr(19, sText, " "))
        Else
            sGreater19 = Mid(sText, InStrRev(sText, " ", 19))
        End If
        sGreater19 = Left(sGreater19, Len(sGreater19) - Len(sGreater55))
    End If

    ' Now lets build the complete string
    SetString = Left(sText, Len(sText) - (Len(sGreater19) + Len(sGreater55))) & vbLf & sGreater19 & vbLf & sGreater55

End Function

I added a check for SPACE character before the number of characters needed as well just in case there isn't a space after the character count. You can take this out if you want

To use the UDF, just call it with your string and it will return the expected string

If the string passed to the UDF already has a LineFeed character in it, UDF will return more than the 3 lines you expect