How to create new rows when a cell has data separated by a comma

Hi Everyone,

I need some help here to either automate or write certain formulas whereby I need to create a new row whenever 1 of the cell have data seperate by comma.

E.g.

Please Login or Register  to view this content.

The end result should look like this

Please Login or Register  to view this content.

someone have a similar questions and solution in another thread
https://www.excelforum.com/excel-for...ml#post4625320

My dataset is about 100,000 - Less than a million records but I've to do it every quarter.
I'm wondering if VBA/Macro would be a better choice. If so how do i go around exploring it since i'm relatively new to VBA / Macro

Btw, i'm using Excel 2016

Using Glenn's solution:

on Sheet2 :

in B2

=IFERROR(TRIM(MID(SUBSTITUTE(","&INDEX(Sheet1!$B$2:$B$5,MATCH(A2,Sheet1!$A$2:$A$5,0)),",",REPT(" ",125)),125*COUNTIF($A$2:A2,A2),125)),"")

With "helper" column in F of Sheet1.

Thanks John,

I looked through your fomula, i'm trying to understand why "125' ? I mean what does "125" mean in this formula

Please Login or Register  to view this content.

@mehmetcik,

Thanks for this code. Does this Macro, create a new workbook after running the macro?
Or is creating a new worksheet?

The 125 is "just" a number which is likely to be longer than any subset of the string to ensure that the extracted strings don't overlap: often the"default" is 255 but others use 100- take your pick!

The formula is credited to Glenn not me.

@John,

Thanks, I understand the formula now

@mehmetcik,

apologize for not being clear. If i were to use the first Macro, will it remove the original data?

Thanks. I tried the formula and it works perfect.
Next step is working on the Macro

@mehmetcik... Sorry, just a few more questions on your 2nd Macro;
Seem like the new data is populated at column G?
If I don't know what is the last row of my data, what is the syntax to replaced '5'?

I haven to get the chance to try it at work, prob tries it over the weekend.

Please Login or Register  to view this content.

I assume the '5' is because there is only 5 row of data in the sample sheet but my original data have more than 5 rows and varies across each sheet. What should be replaced to run till the last available data?

@mehmetcik ... Thanks for educating me on this appreciate your help

@Moderator How do i mark this thread as closed or solved?