Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Split one cell into many
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 DataText 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? |
#2
|
|||
|
|||
Split one cell into many
On Sun, 16 Oct 2005 16:43:04 -0700, "BCBC"
wrote: 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 DataText 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 __________________________ |
#3
|
|||
|
|||
Split one cell into many
Sorry, misread your character limit. Change the 256 in the formula to
250 Rgds On Mon, 17 Oct 2005 10:16:12 +0100, Richard Buttrey wrote: On Sun, 16 Oct 2005 16:43:04 -0700, "BCBC" wrote: 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 DataText 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 Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to split the unmerge cell in Excel? | Excel Discussion (Misc queries) | |||
How do I split one cell of 6 numbers into two cells of 3 and 3? | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
How to split the contents of a cell between two cells. | Excel Worksheet Functions | |||
Can I split a cell diagonally, with text in each triangle ? | Excel Discussion (Misc queries) |