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.