Sub Test()
Dim x As Long, a As Long
Dim strTxt As String, arrTxt() As String
Dim arrTmp() As Variant
Dim z As Long, cnt As Long
Dim arrSp(1 To 1, 1 To 3) As Variant
For x = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arrTxt = Split(Trim(Cells(x, 1).Value), Chr(32))
ReDim arrTmp(0 To UBound(arrTxt), 1)
For a = LBound(arrTxt) To UBound(arrTxt)
arrTmp(a, 0) = arrTxt(a)
arrTmp(a, 1) = Len(arrTxt(a))
Next a
z = arrTmp(0, 1)
cnt = 1
arrSp(1, cnt) = arrTmp(0, 0)
For a = LBound(arrTmp) + 1 To UBound(arrTmp)
z = z + arrTmp(a, 1)
If z <= 30 Then
arrSp(1, cnt) = arrSp(1, cnt) & Chr(32) & arrTmp(a, 0)
Else
arrSp(1, cnt) = Trim(arrSp(1, cnt))
cnt = cnt + 1
a = a - 1
z = 0
End If
Next a
Cells(x, 1).Resize(UBound(arrSp, 1), UBound(arrSp, 2)).Value = arrSp
Erase arrSp
Next x
End Sub
|