Splitting Cells

[b50]

Hello,

I have a spreadsheet full of data like the spreadsheet above. I get this spreadsheet monthly and would like to be able to have it split up with a macro instead of having to do it manually.

I would like the results to just have everything with a Q: be in its own cell, and all of the data after the A: to be deleted.

Can someone help me out?

Thanks to anyone who can help.

[davesexcel]

You could use text to columns and set Q as the delimiter, unfortunately the Q disappears

[shg]

You could split on the line feed character. Do Data > Text to columns, select Delimited, Next, in the Other box press and hold the Alt key, and on the NUMERIC keypad type 0010 (the code for a line feed), Finish. Then delete the "A" columns.

Once you get that down pat, record a macro doing it and we'll help clean it up.

[Leith Ross]

Hello b50,

This macro places each question in an individual cell along with "A:" (an empty answer). There is button on the worksheet to run the macro. Here is the macro code..

Please Login or Register  to view this content.