Pull whole words from a cell up to a set number of characters.

[nmp]

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!

 

[JMB]

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(INDIRECT("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

 

[JMB]

The NG wrapped the post a little funny

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

There is a space in between the quotes.

 

[Ron Rosenfeld]

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!

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 Rosenfeld]

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

For compatibility with the VBA variant, I would make a minor change in the
above:

=REGEX.MID($A1,"\b[\s\S]{1,50}\b",COLUMNS($A:A))

Either will work with Longre's add-in, but I do not think the first will work
for those using the VBScript flavor.


--ron