Lovell Mcilwain wrote:
Hello all,Can anyone tell me how I can split the contents of one cell into separate cells?Right now I have spreadsheet that is formatted with city, state and zip all in the same cell and I need to split it so that cit, state, and zip have there own cell.I wasn't able to find this in the help so Im kinda at a loss. Thanks, Lovell
The following assumes that the column you want to split is in the form: City Name, State Name Zip and the first cell is A1.Where the city can be more than one word and the state is spelled out and could be something like South Dakota. The state and zip are separated by one space.
1. Insert three new columns immediately to the right of the column you want to split.
2. In the first column enter the formula =LEFT(A1;FIND(",";A1;1)-1)
where you replace A1 appropriately.
3. In the second column enter the formula
=MID(A1;LEN(B1)+3;LEN(A1)-LEN(B1)-7)
4. Third column =RIGHT(A1;5)Assumes 5-digit zip code. Adjust for zip + 4 if necessary. If you have both in your table... I didn't try to figure that one out.
5. Drag the formulas down to copy them into all the rows of your table. 6. Select those three columns. Press CNTL-C to copy to clipboard.7. Go to Edit -> Paste Special. Uncheck all the boxes on the left except "Strings". OK. And OK again to the warning box.
8. You can now delete the column that has your original data in it. (After verifying the results of all of the above.)
Good Luck, Merry Christmas, and Hope this Helps -- Rod --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
