Thread: Splitting data from a cell to multiple rows

Dear All,

Faced with a problem. I have steps for doing testing in one cell but I need to put them in multiple rows.

Example
A B C D
1Test Case Name Description Step Expected Result
2 To check user add user check Step 1: Open Sys User added
Step 2: Login admin
Step 3: Add user
Step 4: Logoff

3 To check user del user deletion Step 1: .... User Deleted

Basically in Cell C2 all the steps have been written with carriage returns (Alt + Enter).

The logical sequence that I want is : -
a) Cursor moves to the column
b) Counts the number of sub steps and reduces one from it
c) inserts Blank rows under that row one for each sub step
d) Keeps the first row as it is
e) pastes Step 2 onwards into C3, C4 onwards. No other data to be replicated

Output required is

A B C D
1Test Case Name Description Step Expected Result
2 To check user add user check Step 1: Open Sys User added
3 Step 2: Login admin
4 Step 3: Add user
5 Step 4: Logoff

6 To check user del user deletion Step 1: .... User Deleted


Please note that C3, C4 & C5 now have the relevant steps

I am ok for running the program for each such cell or one shot for all.

Appreciate your help or any questions

Mukul

Hi,

Realized that the formatting was lost. I am attaching an excel file with Input and output

Mukul

Dear Stan,

The Input and Output areas are not the same. Kindly note that in the Input aread D3 has 3 steps written in the same cell separated by carriage return. However, in output area all these steps are now pasted in different rows i.e., D11, D12 and D13.

I am attaching a much bigger file for your ref.

Mukul

Dear Stan,

Thanks for all the help. Problem resolved.

Thanks
Mukul