Split Comma Separated Values or Delimited Data into Separate Rows

Have you ever gotten a spreadsheet from someone or a system where it had one column essentially have a list of items that were separated by commas (or maybe semicolons), but it was tied to the value of another column? Maybe it was a list of items that were sold but was tied to one invoice or sales number. It would be nice to separate out the comma separated list and still tie it to the invoice number. By using Power Query, this can be done in a few easy steps. See how with this video!
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@guruprasad2122]

It helped me immensely, Thanks a ton!

[@DougHExcel]

Glad it helped!

[@sanchitbhadauria1475]

I was stuck with this problem for last 2 weeks, this is super helpful....Thanks Doug🙏

[@DougHExcel]

Hi sanchit bhadauria, glad you liked! Feel free to subscribe to my channel as a there are a bunch of these videos and I have new ones published weekly.

[@Victor-ol1lo]

Your video shows clealry the real power of PQ !! Thanks Doug !!

[@DougHExcel]

Hi Victor Friesen, glad you liked it, thanks for commenting!

[@chinenyeifebirinachi6053]

Thank you so much for this video, it really helped me few mins ago with my large dataset!
You have earned a new subscriber, Dough...

[@DougHExcel]

Thanks for the sub!

[@SIMRANKAUR-eg7nc]

Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos.

[@elenakali7322]

This was exactly what I needed. You saved me a lot of time! Thank you very much!

[@DougHExcel]

You're welcome!

[@shwetbabbu]

Hello, I had spent so many hours today on Google to find this logic and so so happy to finally find your video, super helpful and thanks a ton (have saved it) 😊

[@DougHExcel]

Glad it helped!

[@charleena2319]

Merci merci merci. Thank you so much for your help. I needed this for my work and it's an absolute blessing that you made this video.

[@DougHExcel]

Glad it was helpful!

[@poshnbae4182]

YOU ARE A LIFE SAVER MY MAN!!!! Thank you!!!1

[@DougHExcel]

You’re welcome!

[@cadujucaalmeida1070]

Thanks man ... you save hours of work in my team. Following you and ancious to learn more.

[@DougHExcel]

Glad to help 👍

[@pedervanroestdahl]

Thank you - super helpful and straight to the point. Nice tutorial.

[@DougHExcel]

Hi peder van roest dahl, you're welcome!

[@jenniferanderson1118]

THANK YOU SO MUCH!!!! It truly is so easy to listen to you, this just helped me a ton!

[@DougHExcel]

Glad it helped!

[@johnhuoge]

Thanks @Doug H, saved me a ton of time. I was struggling how to do it with pivots

[@DougHExcel]

Glad it helped!

[@Mehrnaztech]

Thanks @Doug H, this video saved me a ton of hassle. Thank you!🌸

[@DougHExcel]

Glad it helped!

[@skyblue2337]

Thank you for sharing the video on Power Query. Very helpful.

[@DougHExcel]

Glad it was helpful!

[@rajyalakshmipatil6402]

Thanks for the easy explanation

[@DougHExcel]

You are welcome!

[@yasota1]

Thank you very much. Step by step really helped me

[@DougHExcel]

You're welcome!

[@TramNguyen-rv7yc]

Thank you so much, Your video is so easy to understand and works great!

[@DougHExcel]

You're welcome!

[@menjix4639]

what a life saver, had to do the mandatory subscription, hit the notification and like the video to pay respect.

[@DougHExcel]

Hi Menjix, thanks for the kind words and the sub!

[@fjodorgrigorow3851]

Thank you Dough! Was very helpful!

[@DougHExcel]

Glad it was helpful!

[@Gamer_furry_560]

Bro love to you from india, Really Man you have save my lot of time and i done manually work in just 1 min. Thankyou so much Doug 😉😉😉😁😄

[@DougHExcel]

Happy to help!😃

[@ulaganathan4526]

Its simplified my work. Thanks a lot

[@DougHExcel]

You’re welcome!

[@AliMohammad-vz5iy]

Thank you so much for making my life easier ❤

[@DougHExcel]

Glad to have helped!

[@LeeWinbush]

This was a HUGE time saver!

[@DougHExcel]

Glad you thought so!

[@VanessaKnudson]

Thank you so much! My first query and so easy!

[@DougHExcel]

You're very welcome!

[@katiuskakarhula5165]

You just saved hours of work. Thank you soooo much!!!

[@DougHExcel]

Glad it helped!

[@pavol.cernak]

Thanx for this short training. This is what I need today.

[@DougHExcel]

Great to hear!

[@nehasingh3727]

Thanks for this. I used the same concept in Power BI. works exactly the same. Never noticed there could be a split by ROWS option whereas the heading of the tool says 'SPLIT BY COLUMN'. Thanks a ton !

[@DougHExcel]

You're welcome!

[@elainelif]

BLESS YOU DOUG! thank you so much!!!

[@DougHExcel]

You’re welcome!!

[@aaronoli3729]

In need of this ASAP...thank you so much for the help xo

[@DougHExcel]

Glad it helped!

[@JohnSmith-c8d1j]

Thanks, that was easy to follow

[@DougHExcel]

Great to hear!

[@n-power-automotive]

Thank you! This was so helpful!

[@DougHExcel]

Glad it was helpful!

[@akshatashete7978]

Thank you so much for this short video solution..it helped me in my project 🤗

[@DougHExcel]

Glad it helped, thanks for the comment!

[@TheMillennialLifestyleFix]

you saved me hour of work

[@DougHExcel]

Thanks The Millennial Lifestyle Fix, glad it helped!

[@hrushikeshdeshpande1634]

Doug u r a life saver..😂😂😂

[@DougHExcel]

Thanks Hrushikesh Deshpande, glad it helped!

[@DirectionGaming]

Very Good & Simple Sir.
But one thing I want to do is.
-> I want to Keep Original Row as it is. & Below that, want to Create same variation as you created.

[@AbhirupPeeyalSinha]

Thank you a lot for your video saving me at the end moment.

[@DougHExcel]

You are welcome!

[@rakeshmahakud110]

Great explanation thanks 😁👍

[@DougHExcel]

My pleasure!

[@magicalzaza]

Wow it was that simple!

[@patrickmabee882]

This is fantastic and simple to follow you saved the day!!!

[@DougHExcel]

Great to hear!

[@tarasherlock876]

Exactly what I was looking for, thank you for sharing!

[@DougHExcel]

You're welcome!

[@AnshumanMishra1811]

thank you Doug. u saved my day. Awesome

[@DougHExcel]

Glad to help

[@MonicaSmith]

Thank you! First video I came to with this question and you answered it perfectly!

[@DougHExcel]

Glad it was helpful!

[@jessmeregeneralao8419]

Save my 5hrs of work because of this. Thanks a lot

[@DougHExcel]

Thanks Jessmere Generalao, glad it helped!

[@denisegalido6366]

Thank you. You're a lifesaver!

[@DougHExcel]

You're welcome!

[@kirti8292]

Thankyou for sharing this video

[@DougHExcel]

My pleasure!

[@CrazyWorld9597]

It helped for me !! thanks 😀

[@DougHExcel]

You’re welcome!

[@nemo9396]

You're a lifesaver!!

[@DougHExcel]

Thanks for watching!

[@eleonoramaran742]

Thank you for sharing this!

[@DougHExcel]

Hi Eleonora Maran, you're welcome!

[@darshansawant2877]

Thanks for sharing!
Appreciated....

[@DougHExcel]

Thanks for the comment!

[@queilam]

Super mega helpful! thank you so much, I had no idea this exists!

[@DougHExcel]

Glad you liked it!

[@busalem13]

Super, Thank you very much. You save my day!

[@DougHExcel]

Glad I could help!

[@Vitartial]

how to split separated values or delimited data into separate rows

[@DougHExcel]

i think you're referring to line breaks? try custom instead of comma see this one ua-cam.com/video/BxFna32YGNU/v-deo.html

[@chrisklonoski4982]

Thank you! This was amazing and very easy to follow

[@DougHExcel]

You're so welcome!

[@jerryvega5349]

Thank you this was easy to follow!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

[@DougHExcel]

You’re welcome!

[@juansoto7745]

Great! Thank you 🙏🏾 🎉

[@DougHExcel]

You're so welcome!

[@vtbharat4you]

Thank you for upload this video

[@DougHExcel]

Hi VTIndia, you're welcome!

[@nandhithanuts9485]

Thank you very much brother

[@DougHExcel]

Welcome!

[@stevejames6487]

Thank you sir 😃

[@DougHExcel]

You’re welcome!

[@neerajapalla2797]

It's very helpful. Thank you so much Doug.

[@DougHExcel]

Thanks neeraja palla, glad it helped!

[@shekharsatyendrakumar2933]

Thanks for your help, It worked Thanks

[@DougHExcel]

Glad to hear that!

[@supriyadalvi1281]

Thank you so much...sir😊🍫🙌

[@DougHExcel]

Always welcome

[@lysenkiy]

So some and elegant functionality! Thanks for sharing!

[@DougHExcel]

Hi lysenkiy, glad you liked it, thanks for commenting!

[@lysenkiy]

It was supposed to read 'awesome' lol

[@pravinpradhan453]

Thank you helped a lot bro

[@DougHExcel]

You’re the welcome!

[@radykung4376]

very useful . thanks

[@DougHExcel]

You are welcome!

[@nicolasssssssss]

Thank you! I love you

[@DougHExcel]

Hi Nicolás, you're welcome!

[@gnorpflorbsen8665]

What do you do when the source table updates... do you have to manually refresh it every time or will the table update like a formula. If so how do we make it happen automatically like a formula between two sheets would?

[@honeyjoyal1352]

thankyou brother love you

[@DougHExcel]

Hi Honey Joyal, you're welcome!

[@nonoipage3304]

I got new ideas, thanks.

[@DougHExcel]

You’re welcome!

[@tdumtea8548]

thank for your reference

[@DougHExcel]

Hi one pot dum tea, you're welcome!

[@emmafeng55]

Thank you for sharing this is very helpful! :)

[@DougHExcel]

Glad it was helpful!

[@umairm.5662]

Thank You.❤

[@DougHExcel]

You’re welcome!

[@stnanda]

This is really nice! But it's not working when the comma delimited field is numbers. How do you get around that?

[@DougHExcel]

Try this Separate Text and Numbers in Excel
ua-cam.com/video/cA9ypXn37jg/v-deo.html
Or this one
Extract Text in Power Query
ua-cam.com/video/t8HpT9P-rig/v-deo.html

[@isaacnord261]

Is it possible to do thid function in reverse? Essentially I want my end product to be what you had at the beginning.

[@DougHExcel]

Maybe this one ? ua-cam.com/video/v72PR3o09P8/v-deo.html

[@hindyal_masaeed4480]

but it duplicated other values, and all charts i had got messed up and count the values twice for each column i do that for, can we do the trick and we create bar chart which count the items per the unique values

[@mihirhardas8297]

Absolutely great

[@DougHExcel]

Hi Mihir Hardas, thanks for the comment!

[@mihirhardas8297]

@@DougHExcel Hi Dough, you have saved me from staying up at night and figuring this out. Like me many must have benefitted! :)

[@fungo44]

Thanks

[@DougHExcel]

Welcome!

[@pepi1442]

Thank you and god bless :)

[@DougHExcel]

You’re welcome!

[@Creator_Gallary]

Thanks 🎉

[@DougHExcel]

You’re welcome!

[@pearlarasbord]

Great thanks

[@DougHExcel]

Thanks!

[@sandunmadushan1732]

thank you so much

[@DougHExcel]

Hi sandun madushan, you're welcome!

[@ajmalhussain4571]

Thnx u so mxh sir i got too mxh help from this video again thnx

[@DougHExcel]

Always welcome

[@Luciano_mp]

É muito incrível o que esse Power Query pode fazer, obrigado por mais essa aula Doug. Obrigado.

[@DougHExcel]

De nada!

[@Dev_Bartwal]

Very useful thank you so much Share with us this classy video

[@DougHExcel]

Hi MIS Dev Bartwal, thanks for the comment!

[@ManuelLav]

Dear many thanks for these but I would like to know if you know to do but the oposite I mean I have a list of names in column A and I want to have them into a single square/field listed and separated by comma! Do you know by any chance how to do this? Many thanks in advance and take your like

[@DougHExcel]

maybe one of these
ua-cam.com/video/CRx13rXELyM/v-deo.html
ua-cam.com/users/shortsKLFw96yaKME

[@MOATHALSHIBLIvfx]

thank you

[@DougHExcel]

You’re welcome!!!

[@SohailAhmad1]

AMAZING.. THATS GREAT

[@DougHExcel]

Hi @SohailAhmad1, thanks for the comment!

[@TheEmily011293]

Hi Doug, thank you for the explainer video. I need to do the same in Google sheets and I'm struggling. I have watched your sheets version but it doesn't duplicate the data in row one for every delaminated data entry. Do you have any ideas? 🙏

[@angaj]

So what's the workaround? Looking on the same

[@Mohammad_A7i]

Thanks bro

[@DougHExcel]

Welcome!

[@riteshshetty201]

Excellent !!!

[@DougHExcel]

Thank you! Cheers!

[@ShahzadKhan-dp9fp]

thanks a lot

[@DougHExcel]

You’re welcome!

[@abhishekbhattacharya7343]

It saved my life 😂

[@DougHExcel]

Thanks Abhishek Bhattacharya, glad it helped!

[@JoaoPauloDev]

Thank you, helps a lot!!!

[@DougHExcel]

Thanks Joao Paulo Dev, glad it helped!

[@spatil22]

Thank you :)

[@DougHExcel]

You're welcome!

[@jaharashaik3029]

Thank you Doug H. Very nice explanation and It's really helpful....:-)

[@DougHExcel]

Glad it was helpful!

[@sathishc694]

That was awesome

[@DougHExcel]

Hi sathish c, thanks for the kind words!

[@mbusoharvey2447]

@Doug H
Thanks for this video. I have a table which is similar to what you have on the right hand side (i.e. the result you just produced) and I want it in the format you had to your left (with comma separated values). Essentially, I want to to the exact opposite of what you just did here. How do I do that? Thanks

[@DougHExcel]

like this? ua-cam.com/video/W4sK4V9G0GY/v-deo.html

[@mbusoharvey2447]

@@DougHExcel
Thanks. But what if you have two columns for example column 1 contains Invoice_ID and column 2 contains product_ID? And I want all products under one invoice to be in the same row as the invoice_ID and separated by commas. Is that possible? Literally reverse engineering what you just did on this particular video. Thanks

[@levynoordermeer9107]

If you have the office 2016 professional plus version the rows option under advanced doesn't exist unfortunately. Do you no of any way to overcome this?

[@DougHExcel]

Might need to upgrade, otherwise try to split with delimiter then unpivot.

[@levynoordermeer9107]

@@DougHExcel Thanks for the answer, I got it to work with a macro but I will try both:)

[@H3Y_P0KK1E]

So here's my issue. I have a single cell I get from a public url that has all the data in it separated by commas. I'm trying to take that cell and have it break out into rows and columns correctly. I'm not sure what that's called or where to begin. Anyone have an idea where i could get some help on this? Ty so much. and great video.

[@DougHExcel]

hopefully there's some pattern to it, otherwise it's a manual effort. If there is some pattern to the comma separation maybe one of these videos can help
ua-cam.com/video/QKRSF7TM88M/v-deo.html

[@najibbabul2852]

I cant see this option under the Data tab in Excel for Microsoft 365.

[@riteshshetty201]

Mind blowing!!

[@DougHExcel]

PQ is awesome!