As Ron pointed out, I had forgotten about replacing the Line Feed with a
blank... I didn't even think about words longer than 20 characters. Here is
my code modified to account for both of these (I chose to do what Ron did
and just allow 20 character or longer words to be presented as is)...

Sub SplitText20()
Dim R As Range
Dim x As Long
Dim Text As String
Dim Twenty As String
Set R = Range("H1")
Text = WorksheetFunction.Trim(Replace(Range("A1").Value, vbLf, " "))
Do While Len(Text) 0
Twenty = Left(Text, 20)
If InStr(Twenty, " ") = 0 Then
R.Value = Trim(Left(Text, InStr(Text, " ")))
Text = Trim(Mid(Text, InStr(Text, " ")))
ElseIf Mid(Text, 21, 1) = " " Then
R.Value = Trim(Twenty)
Text = Trim(Mid(Text, 21))
ElseIf Len(Twenty) < 20 Then
R.Value = Trim(Twenty)
Exit Do
Else
R.Value = Trim(Left(Twenty, InStrRev(Twenty, " ")))
Text = Trim(Mid(Text, InStrRev(Twenty, " ")))
End If
Set R = R.Offset(1)
Loop
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This macro should do what you asked for...

Sub SplitText20()
Dim R As Range
Dim X As Long
Dim Text As String
Dim Twenty As String
Set R = Range("H1")
Text = WorksheetFunction.Trim(Range("A1").Value)
Do While Len(Text) 0
Twenty = Left(Text, 20)
If Mid(Text, 21, 1) = " " Then
R.Value = Trim(Twenty)
Text = Trim(Mid(Text, 21))
ElseIf Len(Twenty) < 20 Then
R.Value = Trim(Twenty)
Exit Do
Else
R.Value = Trim(Left(Twenty, InStrRev(Twenty, " ")))
Text = Trim(Mid(Text, InStrRev(Twenty, " ")))
End If
Set R = R.Offset(1)
Loop
End Sub

--
Rick (MVP - Excel)


wrote in message
...
On Nov 20, 3:58 pm, "RB Smissaert"
wrote:
It all depends on what you define as a word.
If words are separated by spaces or linebreaks then you could do
something
like this:
Replace all linebreaks with spaces.
Replace all double spaces (chr(32) & chr(32) ) with single spaces.
Then do a split (look up in the VBA help if you don't know the Split
function)
on the space character and you have all your words in an 0-based 1-D
array,
so you can loop through that array and put the words in rows.
This will give you some idea how to approach this.

RBS

wrote in message

...

Hi, I need some help on this.

I have sentence i column "A" row 1. This can be a variable length.
In column "H" I have the column set to receive for now 20 characters.
I'm trying to take the entire sentence and parse it to rows in column
"H"
Each row would have whole words.

This is the start of the code I'm working with.

Sub Mytest()

LENGTHOFCELL = Len(ActiveCell)
cnt = 20 '' nr of characters
cnt1 = 1''set first character
rcnt = 1''row nr
Do While cnt < LENGTHOFCELL
mystring = Mid(ActiveCell.Value, cnt1, 20)
Cells(rcnt, 8).Value = firstname
cnt1 = cnt + 1
cnt = cnt + 20
rcnt = rcnt + 1
mystring = ""
Loop

End Sub

This works some what. I know that this code will exit when the cnt
exceeds the LENGTHOFCELL.
Any help would be appreciated.

Thanks for the reply and you code works. How ever what I'm looking for
is something like this:
The sentence is : This is a test not that I really want it to be a
test.
The sentence will be variable and I have column "H" set to currently
for testing to 20 characters.
As for splitting the word if my variable happens to be set so that it
see "really" as "real" the I want "really" to go to the next row and
the next row can only hold 20 characters.ect..

In column "H"

This is a test
not that I really
want it to be
a test