Excel 2000 - Split Contents of Cell Across Multiple Cells

[Guest]

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.

 

[Bernard Liengme]

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

 

[Guest]

The Name and address appear in one cell with a return at the end of each line.

 

[Bernard Liengme]

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 Liengme]

oops
=SUBSTITUTE(Sheet1!A1,CHAR(10),"*")

 

[RagDyer]

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.

 

[Guest]

That worked. Thanks for your help.

oops
=SUBSTITUTE(Sheet1!A1,CHAR(10),"*")

 

[Gord Dibben]

Select the column and Format>Cells>Alignment. 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 Data>Text to Columns>Delimited by>Other.

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