Tags: break, cell, cells, character, characters, chunks, colums, excel, microsoft, msdn, ranging, sentences, separate, seperate, software, split, splitting, text, word

Split text cell into seperate colums without splitting up a word

On Microsoft » Microsoft Excel

1,573 words with 1 Comments; publish: Thu, 22 May 2008 22:27:00 GMT; (306250.00, « »)

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

All Comments

Leave a comment...

  • 1 Comments
    • 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

      >--Original Message--

      >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

      >.

      >

      #1; Thu, 22 May 2008 22:28:00 GMT