split details in a cell into different cells.

**Madridbear** · 05-10-2007, 08:26 AM

I have some data on a spreadsheet. An example follows.

Mr John Smith, "1 Main Street", "london", "", "NW1 1AA", "UK", ""
Mr David Jones, "1 High Road", "Cardiff", "", "C1 1AA", "UK", "W"
Mr Ian Nesbit", "1 Hill Road", "Alexandria", "Dumbartonshire", "", "", "S"

All the data is in cell A.

Is there a way to split the data? As it is in ""'s and even where no data is available there are " " to show that some info is missing.

Any help appreciated.

I am using xlsx (Excel 2007)

**NBVC** · 05-10-2007, 08:40 AM

If your data is exactly as you show then you can do it in a few steps using Data|Text to Columns.

1. Select Col A and go to Data|Text to columns
2. Select Delimited and click Next
3. Select comma and click Finish.

4. Now select Column B and go to Data Text to columns
5. Select Delimited and click Next
6. Select Other and enter a double quote " and click Next
7. Select the first column in the large white box and select "do not import (skip)"
8. Repeat for the 3rd column.
9. Click Finish

10. Repeat 4-9 for other columns with "" in them