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

[Guest]

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

 

[Guest]

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.

 

[Jason Morin]

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

 

[Guest]

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

 

[Guest]

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