Break out values from a cell with multiple values

[AttuAk]

I need to break out a cell with multiple values into individual rows. For example in my attached worksheet I have a cells in column J that have several comma separated values in each cell. How can I break those out into a table that retains all of the accompanying data with it, i.e., all of the other columns of data, and then shows each value in an individual row? This will eventually allow me to create a pivot table of the values when column J is broken out into individual rows.
The formulas I am using seems to insert blank rows and separates values after a few rows. I'm not sure what I am doing wrong or what I am missing.
Any thoughts or direction here?
Thank you.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect - 16 means nothing. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[AttuAk]

I just updated my profile. I'm using Excel version - MS 365 Apps for Enterprise

[Pete_UK]

That looks like one of my files. As you have a comma at the end of your csv list in column J, you should change the formula in AJ2 to this:

=LEN(J2)-LEN(SUBSTITUTE(J2,",",""))

then copy it down.

Hope this helps.

Pete

[alansidman]

In power query, you can split a cell into multiple rows and the rest of the data in the row will populate those new rows.

https://www.youtube.com/watch?v=tdblfzJ2mEY

[AttuAk]

Thank you Pete. This is indeed one of your files, found it in an earlier post. Thanks for the update. I used your suggestion and then updated the formula used in the cell to break out the multiple cell values, I updated this by adding a 'Clean' statement since my data had hard returns and then added 500 spaces in the REPT statement, in place of 100, since the data I have has as much as 10-12 values in one cell and results in a longer text strings when this formula is applied.
After doing this, it seems to work fine. Once again. Thank you for your posting the original file and now for your update. Very helpful.

[AttuAk]

Thank you Alan for the YT video. This could also be a solution. Thank you

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[AttuAk]

Thank you. I've done both of your suggestions. Appreciate the help and the direction here.