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

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

hi,
date>text to columns. use fixed width. set the break where
you want it by clicking on the number like just above
where you want the break.

>-----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'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


>-----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
>.
>

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(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
>
>
> >-----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(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
> >
> >
> > >-----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
> > >.
> > >
> >

Hi Jason
this worked perfect for me...

Thanks & Regards