Parse string and move data to multiple rows

[vbvamsi]

Hi, I have a column which have values like "Any|Red|Blue|Green|Purple". Excel provides options to split this into columns, but I want to split it into new rows (without overwriting). Can someone help me with this?

[Charles]

vbvamsi,

What you are saying does not make sense. The values you mentioned are already in their own row.

[vandan_tanna]

Somebody else might have a more elegant solution, but here is what I would do:

1. Split into columns with excel's text to columns
2. Copy result --> paste special --> transpose. This will get it into rows.

Of course, you will have to make sure you do not overwrite anything.

[vbvamsi]

Hi vandan_tanna, I tried transpose, but it is overwriting the data in the below rows. I want it to be copied into new rows. Below is an example.
This will be the original table
id text1 text2
id1 a e
id2 b f

This should be changed to
id text1
id1 a
id1 e
id2 b
id2 f

[Charles]

vbvamsi,

Perhaps a workbook would help.
With "Before" and "After"

[vandan_tanna]

This example looks much different one than the one in your original post.

Please upload the excel file so we can see your actual data layout (or if the data is proprietary then make up mock data) and propose a proper solution?

[vbvamsi]

yes. In my original post, I had all the text in 1 cell. But I was able to split it into multiple columns to make things easier.
Anyways I solved the problem. I copied everything to notepad++ and changed the format easily and pasted back to excel. Now everything looks exactly like I wanted. took only a minute in notepad++ .