Spilting Data in the same cell

[Guest]

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.

 

[Guest]

check out the data menu;text to columns

 

[Guest]

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

 

[Roger Govier]

Hi

I don't think the Data>Text 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 C22, then copy B22 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 Special>Values
to "fix" the data and change the formula to actual addresses.
Now, mark the top row, Data>Filter>Autofilter>use dropdown on column
B>Select (Blanks)
Mark the block of visible rows>Delete

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.