Separator to row

[mannerheim]

Hello,

I have an excel file that has in a certain column, different values separated by the pipe character ("|"). I want to create a new row, for each of these values. To be more accurate, I'll give you an example:

Excel file:

Id Name Products
1 John product1|product2|product3
2 Maria product5|product6|product7


And I want it to be:

Id Name Products
1 John product1
1 John product2
1 John product3
2 Maria product5
2 Maria product6
2 Maria product7

I hope this example was more explanatory.

Thank you for your help!
M.

[StephenR]

I suggest you post a small sample workbook.

[snb]

Please Login or Register  to view this content.

[mannerheim]

Here you go.


LE:
@snb, your code gives me a type mismatch, error 13.

[StephenR]

Try this:

Please Login or Register  to view this content.

[snb]

I forgot to give you permission tot adapt the code..

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[mannerheim]

@StephenR, your solution seems to work
Just one last question, since I am new to VBA:
What piece of code sets the column that contains the pipe separator?
I'm asking this question because some of these files have 3-4 or even 5 extra columns.

Thanks!

[StephenR]

This line sets it at two columns to the right of A which is the first column of data, so you could just change the offset:

Please Login or Register  to view this content.

[mannerheim]

So, if I have to copy more than those three columns of data, I have to change the offset?
I mean, if I have more than three columns in my excel file, say ten of fifteen and I have to copy all that data, I have to change this as well:

Please Login or Register  to view this content.

?

[StephenR]

The offset used is the difference between the first column of data and the column with the separated data. Is the separated data always the last column? You'll also have to change the resize but hard to be precise as can only go on the sample you provided.

[mannerheim]

I've attached another sample file, to better understand what I am talking about.

Thanks!

[StephenR]

Please Login or Register  to view this content.

[mannerheim]

Thanks, this is perfect!