Insert a new row if cell contains certain characters

[jujudondon]

Good morning,

I just need a little help to get started with a project I'm working on. I'm trying to transform a Excel spreadsheet that is used for reference and turning it into a file that can be imported into Access.

We have an old billing system and it contains codes that we use to determine a service package. The problem is that one code can be used for multiple packages.

There is an example of what I mean....

Site	BillCode	Package
NYC	X123	6 months @ $19.95
NYC	Y987	3 months free & 6 months @ 19.95
NYC	G555	1 month free + 3 months @ 15.99 + 6 months @ 19.95

What I want to do is have a macro that will evaluate if there are any "+" or "&" in the last column of the row, if there is, I want to copy this row and insert it on the next line so that is looks like this.

Site	BillCode	Package
NYC	X123	6 months @ $19.95
NYC	Y987	3 months free & 6 months @ 19.95
NYC	Y987	3 months free & 6 months @ 19.95
NYC	G555	1 month free + 3 months @ 15.99 + 6 months @ 19.95
NYC	G555	1 month free + 3 months @ 15.99 + 6 months @ 19.95
NYC	G555	1 month free + 3 months @ 15.99 + 6 months @ 19.95

My reason to do this is that I want each code to have 1 package. If a code has more than one package, I need to create as many rows to accomdate each package that it comes with. So my logic is for the macro to count how many "+" or "&" there are in the last cell and create X number of rows. So if the cell contains one "+", copy the row and insert it below. If it contains 2 "&", then copy the row and insert it below 2 times. And so on.

Ultimately, my goal is to have the spreadsheet look like this, but I realize that may be hard to do, so I'm willing to go in and manually fix this last part to have it where I want it.

Site	BillCode	Package
NYC	X123	6 months @ $19.95
NYC	Y987	3 months free
NYC	Y987	6 months @ 19.95
NYC	G555	1 month free
NYC	G555	3 months @ 15.99
NYC	G555	6 months @ 19.95

Any help will be greatly appreciated. By the way, I'm using Excel 2010.

[Kaper]

And here goes my proposition ("do_it_all") in small body:

Please Login or Register  to view this content.

[nilem]

or

Please Login or Register  to view this content.

[jujudondon]

Thanks everyone...both solutions worked like a charm! Thanks for giving me a spring board to resolve this issue.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Assumes you have 3 columns as in your sample.

[jujudondon]

If I have more, what would I need to change?

[jujudondon]

One last question, nilem....

What values would I change if I have more than 3 columns and I need to look in a different column other than "C"?

[JOHN H. DAVIS]

Would your Package Column be D?

[jujudondon]

No, column J