I have a worksheet with a column listing addresses. Unfortunately, the
whole address in each case is entered in a single cell with just commas
separating the different lines, ie:

John Smith, 5 High Street, Anytown, Any County, AB12 CD5

How can I extract the data in these cells and place them in separate
columns, so that the address in cell A1 separates to:

A2 becomes John Smith
A3 becomes 5 High Street
etc, etc

Many thanks.

First, select your column of data and leave it selected through the next two
steps.

1) Click Edit/Replace on Excel's menu bar. Put a comma followed by a space
in the "Find What" field (make that all you type is those two characters).
Next, put just a single comma in the "Replace With" field. Finally, click
the Replace All button and then close the dialog box.

2) Click Data/Text To Columns on Excel's menu bar. In the "Step 1 of 3"
dialog, select the Delimited OptionButton and then click the Next button. In
"Step 2 of 3" dialog, put a check mark in the Comma CheckBox and then click
the Finish button.

Your data should be split into the columns the way you want now.

--
Rick (MVP - Excel)


"Terry Bennett" wrote in message
...
I have a worksheet with a column listing addresses. Unfortunately, the
whole address in each case is entered in a single cell with just commas
separating the different lines, ie:

John Smith, 5 High Street, Anytown, Any County, AB12 CD5

How can I extract the data in these cells and place them in separate
columns, so that the address in cell A1 separates to:

A2 becomes John Smith
A3 becomes 5 High Street
etc, etc

Many thanks.

Many thanks Rick - that's it!



"Rick Rothstein" wrote in message
...
First, select your column of data and leave it selected through the next
two steps.

1) Click Edit/Replace on Excel's menu bar. Put a comma followed by a space
in the "Find What" field (make that all you type is those two characters).
Next, put just a single comma in the "Replace With" field. Finally, click
the Replace All button and then close the dialog box.

2) Click Data/Text To Columns on Excel's menu bar. In the "Step 1 of 3"
dialog, select the Delimited OptionButton and then click the Next button.
In "Step 2 of 3" dialog, put a check mark in the Comma CheckBox and then
click the Finish button.

Your data should be split into the columns the way you want now.

--
Rick (MVP - Excel)


"Terry Bennett" wrote in message
...
I have a worksheet with a column listing addresses. Unfortunately, the
whole address in each case is entered in a single cell with just commas
separating the different lines, ie:

John Smith, 5 High Street, Anytown, Any County, AB12 CD5

How can I extract the data in these cells and place them in separate
columns, so that the address in cell A1 separates to:

A2 becomes John Smith
A3 becomes 5 High Street
etc, etc

Many thanks.