Split text cell into seperate colums without splitting up a word

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have text cells with sentences ranging from 0 to 160 characters long. I
want to break these into 40 character chunks (in separate cells), but don't
want to split any word in half. ie, if the 40 char mark is in the middle of
the work, I want to go backwards, find where the word starts and split from
that point. It's exactly like a wrap text -- but I want to split those lines
up into separate cells.
thanks
kaf
 
If your sentence were in A1, use these 4 formulas:

B1:
=LEFT(A1,MAX((MID(A1,ROW(INDIRECT("1:40")),1)=" ")*ROW
(INDIRECT("1:40"))))

C1:
=LEFT(TRIM(SUBSTITUTE(A1,B1,"")),MAX((MID(A1,ROW(INDIRECT
("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)

D1:
=LEFT(TRIM(SUBSTITUTE(A1,B1&C1,"")),MAX((MID(A1,ROW
(INDIRECT("1:40")),1)=" ")*ROW(INDIRECT("1:40")))-1)

E1:
=SUBSTITUTE(A1,B1&C1&D1,"")

All of them are array formulas except the one in E1.
Array formulas require you to press ctrl/shift/enter.

HTH
Jason
Atlanta, GA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split Long Text Cell into Two Shorter Cells Without Splitting Word 1
Split single cell data into multiple colums 3
Split text string using mid-search? 3
Split individual cells? 2
Split cell without breaking word? 8
Split Cells 2
How to split a cell into 3 columns 2
How to split number and text string to separate cells? 5

Back
Top