SplitJoin.vba

' align = 0 join head (align left)  {"1 2","3","4"}
' align = 1 join tail (align right) {"1","2","3 4"}
Function SplitJoin(ByVal s As String, delim As String, count As Integer, align As Integer)
    Dim result() As String
    ReDim result(0 To count - 1)

    parts = Split(s, delim)

    u = UBound(parts)
    Z = u + 1 - count

    If Z < 0 Then
        If align = 1 Then
            For i = 0 To u
                result(i - Z) = parts(i)
            Next
        Else
            For i = 0 To u
                result(i) = parts(i)
            Next
        End If
    Else
        If align = 1 Then
            For i = 0 To count - 1
                result(i) = parts(i)
            Next
            For i = count To u
                result(count - 1) = result(count - 1) + delim + parts(i)
            Next
        Else
            result(0) = parts(0)
            For i = 1 To Z
                result(0) = result(0) + delim + parts(i)
            Next
            For i = Z + 1 To u
                result(i - Z) = parts(i)
            Next
        End If
    End If

    SplitJoin = result

End Function

Function StringArrayToString(a)
    t = """" & a(0) & """"
    For i = 1 To UBound(a)
        t = t & "," & """" & a(i) & """"
    Next
    StringArrayToString = "{" & t & "}"
End Function

Sub main()

Dim s As String

s = "1 2 3 4"

Debug.Print StringArrayToString(SplitJoin(s, " ", 3, 0)) ' {"1 2","3","4"}
Debug.Print StringArrayToString(SplitJoin(s, " ", 3, 1)) ' {"1","2","3 4"}
Debug.Print StringArrayToString(SplitJoin(s, " ", 4, 0)) ' {"1","2","3","4"}
Debug.Print StringArrayToString(SplitJoin(s, " ", 4, 1)) ' {"1","2","3","4"}
Debug.Print StringArrayToString(SplitJoin(s, " ", 6, 0)) ' {"1","2","3","4","",""}
Debug.Print StringArrayToString(SplitJoin(s, " ", 6, 1)) ' {"","","1","2","3","4"}

End Sub