The NG wrapped the post a little funny

&LEN(A1))),1)=" ",

There is a space in between the quotes.


"JMB" wrote:

One way - if your data is in A1 and assuming you only have to split it once
(ie your second string will surely have < 50 characters or you don't need to
split it), try this in B1:

=LEFT(A1,IF(LEN(A1)50,MATCH(50,IF(MID(A1,ROW(INDI RECT("1:"&LEN(A1))),1)="
", ROW(INDIRECT("1:"&LEN(A1)))))-1,LEN(A1)))

array entered with Cntrl+Shift+Enter

then in C1
=SUBSTITUTE(A1,B1,"")

There will be a leading space in C1. If desired use
=TRIM(SUBSTITUTE(A1,B1,""))
instead to remove it

If you then have to split the result in C1, you could use the first formula
on it.


"nmp" wrote:

How can I pull up to a set number of characters from a cell without cutting
off a word? I have several cells that contain more than 50 characters, but
the database I am importing these cells to has a limit of 50 characters for
that field. I can put anything over 50 charaters in additional fields, but I
do not want to cut off words.

For example I have a cell that contains: 11 x 8-1/2, 80# Patient Education
Shell #501324 CREAM. If I just do a LEFT(A2,50) it leaves off the EAM in
CREAM. I want it to leave off the whole word and put it in a different cell.
Make sense? Is this possible?

Thanks!