threw this together real quick because i have to leave, so i'm not sure it
will work for you or not. maybe you'll get an idea from it.

Sub Mytest()

Dim LENGTHOFCELL As Long
Dim cnt As Long
Dim cnt1 As Long
Dim z As Long
Dim x As Long
Dim rcnt As Long
Dim mystring As String

x = 1
Columns("H").ClearContents
LENGTHOFCELL = Len(Range("A1"))
cnt = 20 '' nr of characters
cnt1 = 1 ''set first character
rcnt = 1 ''row nr
mystring = Mid(Range("A1"), 1, 20)
Do While cnt < LENGTHOFCELL
'
If Right(mystring, 1) = " " Then
Range("H" & rcnt).Value = mystring
x = x + 20
Else
z = InStrRev(mystring, " ", Len(mystring))
Range("H" & rcnt).Value = Mid(mystring, x, z)
x = z
End If
cnt = cnt + z
rcnt = rcnt + 1
mystring = Mid(Range("A1"), x, 20)

Loop
Range("H" & rcnt).Value = mystring
End Sub



--

Gary
Excel 2003


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