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

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))
--


Gary


"Lynnie" wrote in message
...
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

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



"Gary Keramidas" wrote:

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))
--


Gary


"Lynnie" wrote in message
...
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

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

"Lynnie" wrote:

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



"Gary Keramidas" wrote:

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))
--


Gary


"Lynnie" wrote in message
...
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

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)



Lynnie wrote:

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

"Lynnie" wrote:

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



"Gary Keramidas" wrote:

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))
--


Gary


"Lynnie" wrote in message
...
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




--

Dave Peterson