On Wed, 11 Oct 2006 14:07:01 -0700, nmp wrote:

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!

OOps, sorry, the solutions in the other thread were posted later than this one.
In any event, multiple postings for the same question tend to fragment your
responses. And there are two other solutions in the other thread. Here's
mine:

One way would be to use "Regular Expressions"

If your string length will be less than 256 characters, you could download and
install Longre's free morefunc.xll add-in from http://xcell05.free.fr

Then use this formula:

A1: your_string
B1: =REGEX.MID($A1,"(?s)\b.{1,50}\b",COLUMNS($A:A))

copy/drag right as needed.

Note that the Columns($a:a) parameter is a counter that indicates which
instance of up to 50 character strings to return. If you were putting the
formula in A2:An, you should change that argument to ROWS($1:1). Or you could
just manually enter 1, 2, ... n.

If the strings might be longer than 255 characters, a UDF will allow you to use
a similar regular expression to accomplish the same thing. I can post that if
necessary.

Either of the above can also be done in VBA.


--ron
--ron