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!