VBA Split String into 2 groups-CodePudding

my string may have short or long name like below

short: String = "Stack Over Flow"
long: String = "Stack Over Flow Access VBA Coding"

now I need to split this string into 2 groups as 1st group will have first 3 words Stack Over Flow and 2nd group will have Access VBA Coding if the string is short then 2nd group will have blank

below code does not work need your help

Dim str As String, Result As String
Dim Start_Point As Long, No_Characters As Long
str = cmbName.Text
Start_Point = InStr(str, " ")   1
No_Characters = Len(str) - Start_Point

group1 = Left(str, No_Characters   1)
group2 = Right(str, No_Characters   1)

MsgBox group1 & " - " & group2

CodePudding user response：

Try using Limit parameter of Split function like this

Private Function SplitInTwo(sText As String) As Variant
    Dim vSplit      As Variant
    Dim sSecond     As String
    
    vSplit = Split(sText, " ", Limit:=4)
    If UBound(vSplit) >= 3 Then
        sSecond = vSplit(3)
        ReDim Preserve vSplit(0 To 2) As String
    End If
    SplitInTwo = Array(Join(vSplit, " "), sSecond)
End Function

Here are some use-cases

Dim vParts      As Variant

vParts = SplitInTwo("Stack Over Flow")
Debug.Print vParts(0) & " - " & vParts(1)   '--> Stack Over Flow -
vParts = SplitInTwo("Stack Over Flow Access VBA Coding")
Debug.Print vParts(0) & " - " & vParts(1)   '--> Stack Over Flow - Access VBA Coding