Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spilting Data in the same cell
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
Cell data format | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |