Spilting Data in the same cell
 

I have a Excel Spreadsheet that contacts both the names and addresses in the
same cell. I want to separate these to items making one cell for the name
and another for the street address, city, state, zip. Is there a way to do
this without having to redo the data by moving it to another cell one record
at a time.

Example:

-Before- -After-
Name Name Address City State Zip
Address

Thanks for any help.

Spilting Data in the same cell
 

check out the data menu;text to columns
--
paul

remove nospam for email addy!



"xwdpuzzler" wrote:

I have a Excel Spreadsheet that contacts both the names and addresses in the
same cell. I want to separate these to items making one cell for the name
and another for the street address, city, state, zip. Is there a way to do
this without having to redo the data by moving it to another cell one record
at a time.

Example:

-Before- -After-
Name Name Address City State Zip
Address

Thanks for any help.

Spilting Data in the same cell
 

OK I went there but I didn't know how to use the wizard. I will figure it
out, I will not let this defeat me :) Thanks for your help

"paul" wrote:

check out the data menu;text to columns
--
paul

remove nospam for email addy!



"xwdpuzzler" wrote:

I have a Excel Spreadsheet that contacts both the names and addresses in the
same cell. I want to separate these to items making one cell for the name
and another for the street address, city, state, zip. Is there a way to do
this without having to redo the data by moving it to another cell one record
at a time.

Example:

-Before- -After-
Name Name Address City State Zip
Address

Thanks for any help.

Spilting Data in the same cell
 

Hi

I don't think the DataText to columns will help you (at least not
directly).
Your present layout suggests that the address appears below the name. Is
it just one cell of address, or multiple rows of address? Also is the
layout consistent - is there the same number of rows between each Name?

If the data is in the form
Roger Govier
Somehouse
Sometown
Somecounty
John Doe
His house
His Town
His County.

(NB Work on a copy of your data - just in case all goes horribly
wrong!!!)

then in cell B2 enter the following
=IF(MOD(ROW()+3,4)=0,INDEX($A:$A,COLUMN()+ROW()-1,1),"")
Copy across through cells C2:D2, then copy B2:D2 down for the extent of
your data.
This is for data in the format of 4 rows per person. If it is more rows,
then change +3,4 to +4,5 etc.
Similarly if it is fewer rows, then reduce to +2,3 etc.

Having done this, copy the whole block of data, and Paste SpecialValues
to "fix" the data and change the formula to actual addresses.
Now, mark the top row, DataFilterAutofilteruse dropdown on column
BSelect (Blanks)
Mark the block of visible rowsDelete

If your data is a name, followed by a row with all of the address in a
single cell, then carry out the above with
=IF(MOD(ROW()+1,2)=0,INDEX($A:$A,COLUMN()+ROW()-1,1),"")
just to column B and copy down.
Having "fixed" the data and deleted blank rows, then mark column B and
use the Text to columns wizard.
--
Regards

Roger Govier


"xwdpuzzler" wrote in message
...
OK I went there but I didn't know how to use the wizard. I will
figure it
out, I will not let this defeat me :) Thanks for your help

"paul" wrote:

check out the data menu;text to columns
--
paul

remove nospam for email addy!



"xwdpuzzler" wrote:

I have a Excel Spreadsheet that contacts both the names and
addresses in the
same cell. I want to separate these to items making one cell for
the name
and another for the street address, city, state, zip. Is there a
way to do
this without having to redo the data by moving it to another cell
one record
at a time.

Example:

-Before- -After-
Name Name Address City State Zip
Address

Thanks for any help.