How to split data in different rows?

I am writing to request help, where I execute the following logic:

Logic:
If price column contains more than 3 separators ‘/’, then split each string value into new rows and highlight the output in yellow.

for further example, please check the attachment below.
separator_extract.xls
Sheet 1 - shows input dataset
Sheet2 - shows expected output

if possible, an macro solution would be desirable as i have large set of dataset but any other guide or assistance would be most appreciated.

thank you for your time and help.

Is this the final presentation or will you be manipulating the data some more after this.

The reason I ask is that often it is easier to do everything at once rather than doing it in discrete steps.

Thank you for your reply. Sheet 2 shows a final presentation of the output. There would be no further manipulation required after this.

Hi..

Your attached workbook does not reflect your Logic..

If price column contains more than 3 separators ‘/’

Yet you have shown the values for "Redri" to be split even though there are only 2 "/" separators?

Nevertheless..



Try this.. result shown on sheet3.. notice how the row with 'apo' in it does not get carried over to the results (based on its lack of "/" separators)..

Please Login or Register  to view this content.

Yes, the number of slashes to separate on is unclear. Neither example has "more than 3."

So:

1. We need correct Separator Limit.
2. The operation MUST be in the original Worksheet.
3. Leave the original Row untouched.
4. The Title7 Column MUST be empty on new Rows.
5. Highlight the new rows AND the original row they were derived from.
6. Do not highlight unaffected Rows.

Hi apo,

Thank you for your reply and providing a solution.

Apology for not making my explanation clearer in the above context. The condition should say the following:
If the prices column contains a separator ‘/’ equals to or greater than 3 then...

I have tested the above solution, however the I am sorry to trouble you further, but the code is currently throwing a error called - runtime error 9 : subscript out of range.

When I click the debug button, it highlights the error on the following line:

HTML Code: 

Also, when the prices column contains more than one cell with separators, then the error is triggered, as shown in the following attached file under sheet1.
separator_v2.xlsm
The attachment above contains sample set of data, in sheet1 which is currently throwing the above error, as explained in this post.

Any further guide or help in terms of resolving this error, would be very much appreciated and if possible to change the logic, so the script does not fail when there are more than 1 rows under prices column containing either 1 or 2 separators.

Thank you for your time and help