If you don't want to go Ron's RegEx route:
For the subsequent fields, you can pass the length of the string returned
from the previous call as the value of the StartPos parament.
Adjust the value of MaxLength as required.
SampleText remans the same for all calls.

Public Function TruncateOnWholeWord2(SampleText As Variant, Optional
MaxLength As Long = 50, Optional StartPos As Long = 1) As String
Dim TempStr As String

TempStr = Mid(SampleText, StartPos)

'No problem, just return the input
If Len(TempStr) <= MaxLength Then
TruncateOnWholeWord2 = TempStr
Exit Function
End If

'OK, need to do some work
'Find the first [SPACE] before the MaxLength +1 char
TruncateOnWholeWord2 = Left(TempStr, InStrRev(TempStr, " ", MaxLength + 1) -
1)

End Function

As for using it, with you first value in E2
F2: =TruncateOnWholeWord2(E2,50)
G2: =TruncateOnWholeWord2(E2,40,len(F2))
H2: =TruncateOnWholeWord2(E2,40,Len(F2)+len(G2))

NickHK

"nmp" wrote in message
...
OK. I'm not that great at VBA and macros. Any chance you can step me
through exactly what I need to do if my data is in E2 through E1587?

Here is some more detail on what I have: I have 3 fields available for my
description. Description Field 1 needs to be limited to 50 characters.
Anything over 50 characters I want to break on the whole word and put in
Description Field 2 which needs to be limited to 40 characters. There are
a
few descriptions that are over 90 characters long, in which case I want to
put anything remaining in Description Field 3. Field 3 needs to be
limited
to 40 characters too, but I do not have any descriptions over 130
characters.
Currently the full descriptions are all in column E.

Hope this helps.

Thanks!

"NickHK" wrote:

Possibly:

Public Function TruncateOnWholeWord(SampleText As Variant, Optional
MaxLength As Long = 50) As String

'No problem, just return the input
If Len(SampleText) <= MaxLength Then
TruncateOnWholeWord = SampleText
Exit Function
End If

'If the 51st char is a [SPACE], then we can take the first 50 chars OK
'And other tests for what you consider the End-Of-Word char
If Mid(SampleText, 51, 1) = " " Then
TruncateOnWholeWord = Left(SampleText, 50)
Exit Function
End If

'OK, need to do some work
'Find the first [SPACE] before the 51st char
TruncateOnWholeWord = Left(SampleText, InStrRev(SampleText, " ", 51) -
1)

End Function

Depending on your requirements, you can add Trim to strip spaces

NickHK

"nmp" wrote in message
...
I need to be able to pull a set number of characters from a cell, but
I do
not what to cut off a word if that set number ends up in the middle of
a
word.

For example I have: 11 x 8-1/2, 80# Patient Education Shell #501324
CREAM
..
By using a LEFT(A2,50) it cuts off the EAM in CREAM. I actually want
to
cut
off the whole word and put it in a different cell. Make sense? Is
that
possible?

Thanks!