Extract/Copy Text from Cell, and insert that text in a new row below

Hi,

I am working with a spreadsheet/csv for importing products into a webstore I am making for the company I set up... on the spreadsheet; it has a "_category" column (column I) in which it contains the structure of the category listing for example: Computing/Laptops/Notebooks
I need to extract and copy some of the text and insert it into the two new rows below so it would look like:

I
1 Computing/Laptops/Notebooks
2 Computing/Laptops
3 Computing


In the spreadsheet, there are around 9000 rows, each with a cell that has the category structure "Tier1/Tier2/Tier3"; all seperated by the "/". So I need it to insert new rows below so it doesn't overwrite the category structure of the product(s) in the row(s) below it. The number of rows on the spreadsheet will change, so I need it to keep running until it reaches the end.

I've attached a small sample from the spreadsheet, with an example for the first product on the list, there must be an easier/quicker way then manually inserting two rows for each product then copy and pasting a portion of the text down each time (note the spreadsheet doesn't contain all the categories being used, just sample of them.

ImportExample.xls

Any help would be hugely appreciated!

I found something similar - but this only inserts a row below a cell containing certain predefined text, rather than extract the data....

http://www.excelforum.com/excel-prog...ific-text.html

Wow, thank you Mike - that seemed to work perfectly on the test spreadsheet. I'll give it a try on the full set of products - Thank you so much!! Much appreciated

Hi Mike! Yeah that worked pretty much perfectly! There is just one little problem I just noticed, a couple of the category structures contain a "/" as part of the category (I've only just realised this when I went to import the test) - so it has split that down too. Is there a little tweak to the code to prevent it looking at the last "/" and only split using the first two "/"s? I've gone through and double checked - the first two tiers of category will definitely not contain a "/", it would only be in the third category tier.

This is what has happened:

Networking/LAN/Switch Tranceivers (GBIC/SFP)
Networking/Switch Tranceivers (GBIC
Networking/LAN
Networking

and

Networking/Wireless Networking/Access Point/Extenders
Networking/Access Point
Networking/Wireless Networking
Networking

Need them to come out as:

Networking/LAN/Switch Tranceivers (GBIC/SFP)
Networking/LAN
Networking

and

Networking/Wireless Networking/Access Point/Extenders
Networking/Wireless Networking
Networking

If it weren't for those pesky category names, your code was spot on!!

I've uploaded a new version of the spreadsheet with the problematic category names added onto the end of the sample so you can see/test it.

ImportExampleV2.xls

Hi Mike - yeah will only ever be three tiers - Main Category/Sub Category 1/Sub Category 2 - it is Sub Category 2 which might occasionally contain another "/" which would need ignoring. Thanks!

Hi Mike! Yeah that worked perfectly!! Thanks, really appreciate your help today!! You're a legend!!

Hi Arlu, Thanks - I did try to mark it as resolved yesterday, but the website seemed to have loads of problems - kept saying Database Errors etc. I think some of my posts may have duplicated too, but thanks for marking it as Solved for me. I've just added to Mike's reputation by clicking the star too Thanks!