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