Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Robin
 
Posts: n/a
Default 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
.


  #2   Report Post  
kevin frisch
 
Posts: n/a
Default

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
.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
####### within a wrap text cell. OrlandoJayHu Excel Discussion (Misc queries) 2 May 18th 05 08:55 PM
On click, copy text into another cell - XL2K Bob the Builder Excel Worksheet Functions 2 March 16th 05 11:03 PM
Copy text from same cell on every sheet to title sheet? Jon Excel Discussion (Misc queries) 2 February 9th 05 04:11 PM
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 08:10 PM
Finding Partial Text in a Cell bob Excel Worksheet Functions 6 December 18th 04 06:03 AM


All times are GMT +1. The time now is 06:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"