| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - Split Contents of Cell Across Multiple Cells
How can I split the contents of a cell across multiple cells. I tried using
the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
Assuming
John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
The Name and address appear in one cell with a return at the end of each line.
"Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
In A1 of Sheet2 use =SUBSTITUTE(A1,CHAR(10),"*")
This gives: John Public*123 Main Street*Anytown, State 00000 Now Data |Text to column can be used with delimiters "*" and comma Leaving you to use Text to Column on State 00000 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... The Name and address appear in one cell with a return at the end of each line. "Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
oops
=SUBSTITUTE(Sheet1!A1,CHAR(10),"*") -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernard Liengme" wrote in message ... In A1 of Sheet2 use =SUBSTITUTE(A1,CHAR(10),"*") This gives: John Public*123 Main Street*Anytown, State 00000 Now Data |Text to column can be used with delimiters "*" and comma Leaving you to use Text to Column on State 00000 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... The Name and address appear in one cell with a return at the end of each line. "Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
Try TTC (Text To Columns) again!
Use "delimited" and then check "other" in the next page of the wizard, And in the "other" box enter the carriage return by holding down <Alt and type 0010 Using the numbers from the num keypad, *not* the numbers under the function keys. In the Data Preview window, you should then see your data split as you wish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "DeeW" wrote in message ... The Name and address appear in one cell with a return at the end of each line. "Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
That worked. Thanks for your help.
"Bernard Liengme" wrote: oops =SUBSTITUTE(Sheet1!A1,CHAR(10),"*") -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernard Liengme" wrote in message ... In A1 of Sheet2 use =SUBSTITUTE(A1,CHAR(10),"*") This gives: John Public*123 Main Street*Anytown, State 00000 Now Data |Text to column can be used with delimiters "*" and comma Leaving you to use Text to Column on State 00000 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... The Name and address appear in one cell with a return at the end of each line. "Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000 - Split Contents of Cell Across Multiple Cells
Select the column and FormatCellsAlignment. Uncheck "wrap text" and OK
Do you see little squares where the CR's are? If so, insert 2 or 3 empty columns to right of column. Then DataText to ColumnsDelimited byOther. Hold ALT key and on the number pad at right, type 0010 You won't see anything but go to Next to see your data split into columns. Click Finish. You may have further massage to break out the State and Zip using Space Delimited Gord Dibben MS Excel MVP On Wed, 8 Nov 2006 11:48:01 -0800, DeeW wrote: The Name and address appear in one cell with a return at the end of each line. "Bernard Liengme" wrote: Assuming John Public 123 Main Street Anytown, State 00000 appear on A1:A3 and the next person is on A4:A6 of Sheet1 On Sheet2 in A1 enter =Sheet1!A1; in B1 =Sheet1!A2, in C1 =Sheet1!A3 On Sheet2 in A2 enter =Sheet1!A4; in B2 =Sheet1!A5, in C2 =Sheet1!A6 Copy A1:C2 down the page for as many rows as there are names (rows in Sheet1 divided by 3) Now use Data | Text to Column on stuff in C column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "DeeW" wrote in message ... How can I split the contents of a cell across multiple cells. I tried using the Text to Columns option in Excel which works, but I have over 200 cells that need to be corrected. The data in the cell appears as: John Public 123 Main Street Anytown, State 00000 I need to appear as: John Public 123 Main Street Anytown State 00000 Any suggestions would be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| Can I split 1 cell into 2 cells in Excel? | Excel Discussion (Misc queries) | |||
| print excel spreadsheet splitting cell contents on multiple pages | Excel Discussion (Misc queries) | |||
| Why does Excel 2000 crash when formatting a cell to Date? | Excel Discussion (Misc queries) | |||
| How do I combine the contents of multiple cells in one cell? | Excel Worksheet Functions | |||
Linear Mode
