I've tested this extensively, but do the same and let me
know if it works. With the text string in A3:
B3:
=IF(LEN(A3)<=40,A3,LEFT(TRIM(A3),MAX((MID(TRIM(A3),ROW
(INDIRECT("1:40")),1)=" ")*ROW(INDIRECT("1:40")))))
C3:
=IF(LEN(A3)<=80,SUBSTITUTE(A3,B3,""),LEFT(SUBSTITUTE(TRIM
(A3),B3,""),IF(MID(SUBSTITUTE(TRIM(A3),B3,""),41,1)
=" ",40,MAX((MID(SUBSTITUTE(TRIM(A3),B3,""),ROW(INDIRECT
("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)))
D3:
=IF(LEN(A3)<=120,SUBSTITUTE(A3,B3&C3,""),LEFT(SUBSTITUTE
(TRIM(A3),B3&C3,""),IF(MID(SUBSTITUTE(TRIM
(A3),B3&C3,""),41,1)=" ",40,MAX((MID(SUBSTITUTE(TRIM
(A3),B3&C3,""),ROW(INDIRECT("1:40")),1)=" ")*ROW(INDIRECT
("1:40")))-1)))
E3:
=IF(LEN(A3)<=160,SUBSTITUTE(A3,B3&C3&D3,""),LEFT
(SUBSTITUTE(TRIM(A3),B3&C3&D3,""),IF(MID(SUBSTITUTE(TRIM
(A3),B3&C3&D3,""),41,1)=" ",40,MAX((MID(SUBSTITUTE(TRIM
(A3),B3&C3&D3,""),ROW(INDIRECT("1:40")),1)=" ")*ROW
(INDIRECT("1:40")))-1)))
F3:
=IF(LEN(A3)<=200,SUBSTITUTE(A3,B3&C3&D3&E3,""),LEFT
(SUBSTITUTE(TRIM(A3),B3&C3&D3&E3,""),IF(MID(SUBSTITUTE
(TRIM(A3),B3&C3&D3&E3,""),41,1)=" ",40,MAX((MID(SUBSTITUTE
(TRIM(A3),B3&C3&D3&E3,""),ROW(INDIRECT("1:40")),1)=" ")
*ROW(INDIRECT("1:40")))-1)))
G3:
=SUBSTITUTE(A3,B3&C3&D3&E3&F3,"")
All formula are array formulas (meaning you must press
ctrl/shift/enter after inserting the formula and any time
you edit the cell) except for the one in G3.
HTH
Jason
Atlanta, GA