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.

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.

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.

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.

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.

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.

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.

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.