Split text cell into seperate colums without splitting up a word

K

kevin_frisch

I have text cells with sentences ranging from 0 to 160 characters long. I
want to break these into 40 character chunks (in separate cells), but don't
want to split any word in half. ie, if the 40 char mark is in the middle of
the work, I want to go backwards, find where the word starts and split from
that point. It's exactly like a wrap text -- but I want to split those lines
up into separate cells.
thanks
kaf
 
J

Jason Morin

If your sentence were in A1, use these 4 formulas:

B1:
=LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:40")),1)=" ")*ROW
(INDIRECT("1:40"))))

C1:
=LEFT(TRIM(SUBSTITUTE(A1,B1,"")),MAX((MID(A1,ROW(INDIRECT
("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)

D1:
=LEFT(TRIM(SUBSTITUTE(A1,B1&C1,"")),MAX((MID(A1,ROW
(INDIRECT("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)

E1:
=SUBSTITUTE(A1,B1&C1&D1,"")

All of them are array formulas except the one in E1.
Array formulas require you to press ctrl/shift/enter.

HTH
Jason
Atlanta, GA
 
Top