Splitting Paragraph into two columns w/limit of 256 characters

[Lynnie]

Hello,

I have a .xls file with one column of 500 characters in each cell. I would
like to split up the cells into two columns, each consisting of 256
characters or less.

Currently, when I split the column into two columns, at the 256 mark it
would split a word into two parts. The first few letters of a word is at the
end of the first cell, and the last few letters is the start of the second
cell.

Ex. for the word transmission
First Cell: XXXXXXXXXXXXXX Transmi
Second Cell: ssion XXXXXXXXXXXXXXX

Is there a way for excel to split the column into two, but keeping the words
together? there are 3,000 rows.

Thanks!
Lynnie

 

[Gary Keramidas]

see if these 2 formulas would help. i used A1 as the cell with the original
data:

=LEFT(A1,FIND(" ", A1,245)-1)

=RIGHT(A1,LEN(A1)-FIND(" ",A1,245))

 

[Lynnie]

Hi Gary!

Thank you for your fast reply!! Yes that worked beautifully! THANKS!

But one strange thing- there is a handful in the first column that are
returning ############## for the =LEFT(A43,FIND(" ", A43,245)-1) formula.

Also- do you know how to remove carriage returns (the little square) that is
in a cell?

Much Appreciated!

Lynnie

 

[Lynnie]

Sorry Gary- Please ignore my previous message.

I know why it's saying invalid. If the first column has less than 256
characters, the result would be "#value!" in the first and second columns

How do you return blank instead of "#value!" in both columns?

Thanks!
Lynnie

 

[Dave Peterson]

How about this in B1:
=LEFT(A1,FIND(" ", A1&REPT(" ",300),245)-1)
and this in C1:
=MID(A1,LEN(B1)+2,300)

(+2 to get past the length of the string in B1 plus that space character)