Separating data in a cell

Hello all,
I've been tasked with separating data within an Excel cell into it's own row. The difficulty I am having is, I need to keep the ticket number each piece of data within that cell is linked to. For example, I have Column A that has the ticket number, and column I has the serial numbers of each piece of equipment linked to that ticket number. I would like the serial numbers separated into their own row, but still keep the ticket number in which that equipment is connected to.

Here's an example: Column A Column I
Ticket # Serial Numbers on Account
23999866 PAFR06598067 - MOPCUDTAM

I would like to modify it into this:

Ticket # Serial Numbers on Account
23999866 PAFR06598067
23999866 MOPCUDTAM


Would I need a formula to do this, or is this something I can do using one of the Ribbon commands? Thank you for any and all assistance with this.

Damian37

could it be done with a sub in VBA?

Originally Posted by rcm

could it be done with a sub in VBA?

I have access to VBA, and I've played around with VBA, but I'm not that proficient that I would know what coding to use that would break out the serial numbers the way I need them broken up.

Originally Posted by Miraun

Could just do a text-to-columns on the data ribbon, using spaces as separators, and remove extra columns... Unless there are oddities outside of the example you posted.

That's what I'm doing now, text-to-columns, but there are about 1500 rows and the text-to-columns creates new columns, which I am now having to transpose in order to place them in new rows, and then copy the correlating data to the new rows. I was hoping to find a quicker way to do this.

Here's an example of the spreadsheet. The ticket numbers are in column A and the serial numbers are in column I. Currently they've been separated into new columns, but I would like them separated into new rows to assist when manipulating pivot tables. It is imperative I separate the serial numbers into new rows, without creating new ticket numbers for the serial numbers in each new row. I hope the spreadsheet I've attached explains my needs a little better than my words. Thanks again for all of your help.

Damian37

hoping this would help, run the macro