Find closest space to the left of specified position and then split

Another way using a macro.....

Sub SplitCellContent()
Dim rng As Range, cell As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & lr)
For Each cell In rng
    n = Len(cell.Value) - Len(WorksheetFunction.Substitute(cell.Value, " ", ""))
    For i = n To 1 Step -1
        If Len(cell.Value) > 40 Then
            m = WorksheetFunction.Find("*", WorksheetFunction.Substitute(cell.Value, " ", "*", i))
            If m <= 40 Then
                cell.Offset(0, 1).Value = Mid(cell.Value, m + 1, Len(cell.Value) - m)
                cell.Value = Left(cell.Value, m)
            End If
        End If
    Next i
Next cell
End Sub