Split one cell into many

[Guest]

I am editing a database in Excel that will be dumped into another program.
Some of my cells in Excel have over 1000 characters of text (in
sentence/paragraph format). The other program has a limit of 250 characters
in each field. This means that I need to split the data of each cell into
multiple smaller cells.

I tried using Data>Text to Columns, but this inserts preset breaks which I
have to move one-by-one. Since my Excel document has almost 1000 rows and
several columns, I'm sure you can imagine how long that would take. Does
anyone know how this can be done quickly?

 

[Richard Buttrey]

I am editing a database in Excel that will be dumped into another program.
Some of my cells in Excel have over 1000 characters of text (in
sentence/paragraph format). The other program has a limit of 250 characters
in each field. This means that I need to split the data of each cell into
multiple smaller cells.

I tried using Data>Text to Columns, but this inserts preset breaks which I
have to move one-by-one. Since my Excel document has almost 1000 rows and
several columns, I'm sure you can imagine how long that would take. Does
anyone know how this can be done quickly?

Assuming you have say A1 with 1024 characters, enter the following in
B1

=MID($A$1,(COLUMN()-2)*256+1,256)

and copy across to E1, or further if A1 has > 1024 characters.

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

Sorry, misread your character limit. Change the 256 in the formula to
250

Rgds

Assuming you have say A1 with 1024 characters, enter the following in
B1

=MID($A$1,(COLUMN()-2)*256+1,256)

and copy across to E1, or further if A1 has > 1024 characters.

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________