How do I break up a long text cell into shorter ones withoug s
 

Hello,

I'm attempting a similar algorithm without much success. I am attempting to
split a 255 character column into columns which are 72 characters wide, also
without splitting words in the middle. I'm not 100% clear on the logic of
the formula, and am having difficulty translating the formula into a
72-character version of the one below. For some reason, nothing is appearing
in the B3 column, and it goes downhill from there. Can anyone please help?

Thank you!
Robin

"Jason Morin" wrote:

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(SUBSTITU TE(TRIM
(A3),B3,""),IF(MID(SUBSTITUTE(TRIM(A3),B3,""),41,1 )
=" ",40,MAX((MID(SUBSTITUTE(TRIM(A3),B3,""),ROW(INDIR ECT
("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)))

D3:

=IF(LEN(A3)<=120,SUBSTITUTE(A3,B3&C3,""),LEFT(SUBS TITUTE
(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(SUBSTITUT E(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,""),LEF T
(SUBSTITUTE(TRIM(A3),B3&C3&D3&E3,""),IF(MID(SUBSTI TUTE
(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


-----Original Message-----
I have a txt cell with between 1 and 200 characters in
lenght. I'm trying to
break it up into 1 to 6 seperate cells, each with up to
40 characters in it
without splitting up any words. So if the 40th character
splits a word, I
want to split it at the previous space. Then I want to
go from that character
forward for the next cell, and so on.

thanks

kevin
.

I tried using Jason's method in the previous posting, and could not get it to
work... never did find a solution...
thanks
kaf


"Robin" wrote:

Hello,

I'm attempting a similar algorithm without much success. I am attempting to
split a 255 character column into columns which are 72 characters wide, also
without splitting words in the middle. I'm not 100% clear on the logic of
the formula, and am having difficulty translating the formula into a
72-character version of the one below. For some reason, nothing is appearing
in the B3 column, and it goes downhill from there. Can anyone please help?

Thank you!
Robin

"Jason Morin" wrote:

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(SUBSTITU TE(TRIM
(A3),B3,""),IF(MID(SUBSTITUTE(TRIM(A3),B3,""),41,1 )
=" ",40,MAX((MID(SUBSTITUTE(TRIM(A3),B3,""),ROW(INDIR ECT
("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)))

D3:

=IF(LEN(A3)<=120,SUBSTITUTE(A3,B3&C3,""),LEFT(SUBS TITUTE
(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(SUBSTITUT E(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,""),LEF T
(SUBSTITUTE(TRIM(A3),B3&C3&D3&E3,""),IF(MID(SUBSTI TUTE
(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


-----Original Message-----
I have a txt cell with between 1 and 200 characters in
lenght. I'm trying to
break it up into 1 to 6 seperate cells, each with up to
40 characters in it
without splitting up any words. So if the 40th character
splits a word, I
want to split it at the previous space. Then I want to
go from that character
forward for the next cell, and so on.

thanks

kevin
.