Splitting a Cell based on Commas and adding new Rows

Dear Excel Users,

I have a situation where I have some Characteristics of a Part mentioned in few cells in Column C (Eg - Cell C5 of the attached Sheet) where 3 characteristics of the part are mentioned separated by ",".

However I want only 1 characteristic per part number in 1 cell, and I want the remaining characteristics to be copied down to the below row with all the other details in Column A & B remaining same.

Please see attached sheet for understanding.

Help Appreciated.

I think this would be relatively easy with a macro, is that an option for you?

@ Cheeky Charlie

Yes.

Macros are welcome.

Something like this would work:

Please Login or Register  to view this content.

Put it in a module, then create a form button on the sheet and point it at the macro.

@Cheeky Charlie

Hey!!

The Code is working fine.. However I need to click an each cell with multiple characteristics and run the macro.

But I am dealing with 10000 such rows and its not possible to run the macro every singly time.

Can the code be altered in such a way where it can be applicable to all such 10000 rows at a time?

Thanx for the help..

That's odd, when I run the code on the example you uploaded it addresses both cells with commas. Can you upload an example which stops after one cell?

Here is the file.

Ive deleted most of it to make the file smaller.

Ive deleted all the cells in Column "E" with characteristics not having ",".

ive also changed the code to adjust to column E

Oh. Adjustment. Simple problem, there is another line of code which references column C:C...

Please Login or Register  to view this content.

hth

Perfect!!

That was awesome.

Marking the Thread as Solved!!

Hi SS113, welcome to the forum, unfortunately...
Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.




.... blah blah

I don't completely agree with this (I think the useful resource can be enhanced with 'variations on a theme' which is often the target of an inbound Googler. (How many of us have identical problems?) Nonetheless that's the rule - most of the time it's for the best!

Please start a new thread, pm me a link. The adjustment is trivial.

@CheekyCharlie

Hi!!

I've run the code you wrote yesterday in actual workbook having 19000 Rows.

The Code is running for 12 Hrs now.

Does it usually take so much time or there is something messed up??

That's quite a lot of data - without screenupdating (below) is about 3 times faster on my machine... but I ran 24,000 rows of your sample data set in a couple of minutes, so I think there might be something wrong!

Withotu knowing more about your situation, if you have a large workbook, it might be worth taking a sheet out to tidy it up to save memory.

Please Login or Register  to view this content.

@CheekyCharlie

After running the code for 18 Hrs is concluded something was wrong.

However 1 error I found out that Column B was full of Formulas which I am removing now.

I tried to run the previous code on a Sheet of 5000 rows, and its doing the job within 1 min.

Wonder why it is taking so much time on the actual sheet..

@CheekyCharlie!!

I tried the new code too.

Its not working on the whole sheet.

Is there a way I can E-Mail the actual file to you so that you can understand it.

I am unable to upload the file on the forum as it is quite large..