how to split multi line cell contents of two column into separated rows in excel using VBA

[Manosub]

Hi All,

I'm Currently facing issue in splitting my Multi-Line cell contents into separate Rows in excel VBA. I tried searching in Internet the solution which i could find is for only one column Multi-line cell, i have two column multi line cell and i want to split the same.

For more clarity I have given the screen shot of my Excel data.
Original Data:


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Req #[/TD]
[TD]Question[/TD]
[TD]Answers[/TD]
[/TR]
[TR]
[TD]AR-003[/TD]
[TD]1.Do we expect any other user at school to have access to this view?
2.Is there an expectation that school users can send reminders for cases which are more urgent?
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR-002[/TD]
[TD]1.Do we expect any other user at school to have access to this view?
2.Is there an expectation that school users can send reminders for cases which are more urgent?
[/TD]
[TD] 1.Programme coordinator would be raising the request I believe. No, reminders will be system driven to staff[/TD]
[/TR]
[TR]
[TD]AR-001[/TD]
[TD]1. What all cases are auto approved?
2. Is there an expectation that auto approved cases can be reviewed and decisions can be changed?
3. How would we differentiate emergency cases and normal advice request?
4. Is evidence upload expected for all cases or this will vary by cases?
[/TD]
[TD]1. BR for this module not fully defined yet but imagine there might be some auto approvals.
2. Yes, same kind of functionality as A and I when it can be manually reviewed and case might be amended but the timescales around these are going to be much tighter
3. We will have to have categories
4. It will vary
[/TD]
[/TR]
</tbody>[/TABLE]









































Needed Output:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Req[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.Is there an expectation that school users can send reminders for cases which are more urgent?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR-002[/TD]
[TD]1.Do we expect any other user at school to have access to this view?[/TD]
[TD] 1.Programme coordinator would be raising the request I believe. No, reminders will be system driven to staff[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.Is there an expectation that school users can send reminders for cases which are more urgent?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AR-003[/TD]
[TD]1. What all cases are auto approved?[/TD]
[TD]1. BR for this module not fully defined yet but imagine there might be some auto approvals.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2. Is there an expectation that auto approved cases can be reviewed and decisions can be changed?[/TD]
[TD]2. Yes, same kind of functionality as A and I when it can be manually reviewed and case might be amended but the timescales around these are going to be much tighter[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3. How would we differentiate emergency cases and normal advice request?[/TD]
[TD]3. We will have to have categories [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4. Is evidence upload expected for all cases or this will vary by cases?[/TD]
[TD]4. It will vary[/TD]
[/TR]
</tbody>[/TABLE]




































If possible can we Merge the Req # in the first column for the question & Answers as one cell?

Please help me out if some one have solution for this as i have to do this manually for more than 100 to 200 line item daily which is taking more time.

Regards,
Manoj

 

[Manosub]

sorry while pasting the needed table got messed. I want to split the cell values of question and answer in each row along with the Req#. so that each question in column B will have its Answers in Column C