Split Comma Separated Values or Delimited Data into Separate Rows

Подписаться 79 тыс.

Просмотров 185 тыс.

50% 1

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...
~-~~-~~~-~~-~

Опубликовано:

16 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 288

@sanchitbhadauria1475 3 года назад

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

@DougHExcel 3 года назад

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.

@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!

@chinenyeifebirinachi6053 4 года назад

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 4 года назад

Thanks for the sub!

@SIMRANKAUR-eg7nc 3 года назад

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.

@shwetbabbu 3 месяца назад

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 3 месяца назад

Glad it helped!

@Victor-ol1lo 6 лет назад

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

@DougHExcel 6 лет назад

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

@poshnbae4182 2 года назад

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

@DougHExcel 2 года назад

You’re welcome!

@neerajapalla2797 Год назад

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

@DougHExcel Год назад

Thanks neeraja palla, glad it helped!

@guruprasad2122 Год назад

It helped me immensely, Thanks a ton!

@DougHExcel Год назад

Glad it helped!

@elenakali7322 6 месяцев назад

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

@DougHExcel 6 месяцев назад

You're welcome!

@charleena2319 11 месяцев назад

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 11 месяцев назад

Glad it was helpful!

@jenniferanderson1118 3 года назад

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

@DougHExcel 3 года назад

Glad it helped!

@cadujucaalmeida1070 3 года назад

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

@DougHExcel 3 года назад

Glad to help 👍

@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!😃

@TramNguyen-rv7yc 3 года назад

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

@DougHExcel 3 года назад

You're welcome!

@pedervanroestdahl 10 месяцев назад

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

@DougHExcel 10 месяцев назад

Hi peder van roest dahl, you're welcome!

@johnhuoge 3 года назад

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

@DougHExcel 3 года назад

Glad it helped!

@skyblue2337 3 года назад

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

@DougHExcel 3 года назад

Glad it was helpful!

@Mehrnaztech 2 года назад

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

@DougHExcel 2 года назад

Glad it helped!

@yasota1 Год назад

Thank you very much. Step by step really helped me

@DougHExcel Год назад

You're welcome!

@ulaganathan4526 2 года назад

Its simplified my work. Thanks a lot

@DougHExcel 2 года назад

You’re welcome!

@jessmeregeneralao8419 5 лет назад

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

@DougHExcel 5 лет назад

Thanks Jessmere Generalao, glad it helped!

@rajyalakshmipatil6402 Год назад

Thanks for the easy explanation

@DougHExcel Год назад

You are welcome!

@DirectionGaming 2 года назад

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.

@katiuskakarhula5165 3 года назад

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

@DougHExcel 3 года назад

Glad it helped!

@akshatashete7978 2 года назад

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

@DougHExcel 2 года назад

Glad it helped, thanks for the comment!

@AliMohammad-vz5iy Год назад

Thank you so much for making my life easier ❤

@DougHExcel Год назад

Glad to have helped!

@AbhirupPeeyalSinha Год назад

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

@DougHExcel Год назад

You are welcome!

@pavol.cernak 4 года назад

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

@DougHExcel 4 года назад

Great to hear!

@nehasingh3727 3 года назад

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 3 года назад

You're welcome!

@VanessaKnudson 11 месяцев назад

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

@DougHExcel 11 месяцев назад

You're very welcome!

@elainelif Год назад

BLESS YOU DOUG! thank you so much!!!

@DougHExcel Год назад

You’re welcome!!

@patrickmabee882 2 года назад

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

@DougHExcel 2 года назад

Great to hear!

@MonicaSmith 3 года назад

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

@DougHExcel 3 года назад

Glad it was helpful!

@TheMillennialLifestyleFix 2 года назад

you saved me hour of work

@DougHExcel 2 года назад

Thanks The Millennial Lifestyle Fix, glad it helped!

@LeeWinbush Год назад

This was a HUGE time saver!

@DougHExcel Год назад

Glad you thought so!

@JohnSmith-c8d1j 9 месяцев назад

Thanks, that was easy to follow

@DougHExcel 9 месяцев назад

Great to hear!

@n-power-automotive Год назад

Thank you! This was so helpful!

@DougHExcel Год назад

Glad it was helpful!

@aaronoli3729 3 года назад

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

@DougHExcel 3 года назад

Glad it helped!

@hrushikeshdeshpande1634 10 месяцев назад

Doug u r a life saver..😂😂😂

@DougHExcel 10 месяцев назад

Thanks Hrushikesh Deshpande, glad it helped!

@queilam 2 года назад

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

@DougHExcel 2 года назад

Glad you liked it!

@tarasherlock876 2 года назад

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

@DougHExcel 2 года назад

You're welcome!

@fjodorgrigorow3851 2 года назад

Thank you Dough! Was very helpful!

@DougHExcel 2 года назад

Glad it was helpful!

@chrisklonoski4982 2 года назад

Thank you! This was amazing and very easy to follow

@DougHExcel 2 года назад

You're so welcome!

@Luciano_mp 6 лет назад

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

@DougHExcel 6 лет назад

De nada!

@nemo9396 2 года назад

You're a lifesaver!!

@DougHExcel 2 года назад

Thanks for watching!

@lysenkiy 6 лет назад

So some and elegant functionality! Thanks for sharing!

@DougHExcel 6 лет назад

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

@lysenkiy 6 лет назад

It was supposed to read 'awesome' lol

@Vitartial 2 года назад

how to split separated values or delimited data into separate rows

@DougHExcel 2 года назад

i think you're referring to line breaks? try custom instead of comma see this one ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BxFna32YGNU.html

@magicalzaza 5 месяцев назад

Wow it was that simple!

@rakeshmahakud110 2 года назад

Great explanation thanks 😁👍

@DougHExcel 2 года назад

My pleasure!

@kirti8292 11 месяцев назад

Thankyou for sharing this video

@DougHExcel 10 месяцев назад

My pleasure!

@denisegalido6366 3 года назад

Thank you. You're a lifesaver!

@DougHExcel 3 года назад

You're welcome!

@AnshumanMishra1811 4 года назад

thank you Doug. u saved my day. Awesome

@DougHExcel 4 года назад

Glad to help

@jerryvega5349 4 года назад

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

@DougHExcel 4 года назад

You’re welcome!

@pookystudio6702 2 года назад

Thanks

@DougHExcel 2 года назад

You’re welcome!

@darshansawant2877 2 года назад

Thanks for sharing! Appreciated....

@DougHExcel 2 года назад

Thanks for the comment!

@vtbharat4you Год назад

Thank you for upload this video

@DougHExcel Год назад

Hi VTIndia, you're welcome!

@CrazyWorld9597 2 года назад

It helped for me !! thanks 😀

@DougHExcel 2 года назад

You’re welcome!

@busalem13 3 года назад

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

@DougHExcel 3 года назад

Glad I could help!

@nandhithanuts9485 3 года назад

Thank you very much brother

@DougHExcel 3 года назад

Welcome!

@stnanda 10 месяцев назад

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

@DougHExcel 10 месяцев назад

Try this Separate Text and Numbers in Excel ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cA9ypXn37jg.html Or this one Extract Text in Power Query ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-t8HpT9P-rig.html

@jaharashaik3029 4 года назад

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

@DougHExcel 4 года назад

Glad it was helpful!

@ajmalhussain4571 3 года назад

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

@DougHExcel 3 года назад

Always welcome

@TheEmily011293 2 года назад

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 2 года назад

So what's the workaround? Looking on the same

@supriyadalvi1281 Год назад

Thank you so much...sir😊🍫🙌

@DougHExcel Год назад

Always welcome

@nonoipage3304 2 года назад

I got new ideas, thanks.

@DougHExcel 2 года назад

You’re welcome!

@shekharsatyendrakumar2933 Год назад

Thanks for your help, It worked Thanks

@DougHExcel Год назад

Glad to hear that!

@honeyjoyal1352 3 года назад

thankyou brother love you

@DougHExcel 3 года назад

Hi Honey Joyal, you're welcome!

@nicolasssssssss Год назад

Thank you! I love you

@DougHExcel Год назад

Hi Nicolás, you're welcome!

@sushilbhandari4261 Месяц назад

Thanks 🙏

@Dev_Bartwal 6 лет назад

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

@DougHExcel 6 лет назад

Hi MIS Dev Bartwal, thanks for the comment!

@tdumtea8548 3 года назад

thank for your reference

@DougHExcel 3 года назад

Hi one pot dum tea, you're welcome!

@stevejames6487 4 года назад

Thank you sir 😃

@DougHExcel 4 года назад

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?

@eleonoramaran742 3 года назад

Thank you for sharing this!

@DougHExcel 3 года назад

Hi Eleonora Maran, you're welcome!

@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 ? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-v72PR3o09P8.html

@umairm.5662 2 года назад

Thank You.❤

@DougHExcel 2 года назад

You’re welcome!

@emmafeng55 3 года назад

Thank you for sharing this is very helpful! :)

@DougHExcel 3 года назад

Glad it was helpful!

@radykung4376 Год назад

very useful . thanks

@DougHExcel Год назад

You are welcome!

@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

@pravinpradhan453 3 года назад

Thank you helped a lot bro

@DougHExcel 3 года назад

You’re the welcome!

@pepi1442 2 года назад

Thank you and god bless :)

@DougHExcel 2 года назад

You’re welcome!

@ManuelLav 2 года назад

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 2 года назад

maybe one of these ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CRx13rXELyM.html ru-vid.comKLFw96yaKME

@najibbabul2852 5 месяцев назад

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

@abhishekbhattacharya7343 3 года назад

It saved my life 😂

@DougHExcel 3 года назад

Thanks Abhishek Bhattacharya, glad it helped!

@mihirhardas8297 3 года назад

Absolutely great

@DougHExcel 3 года назад

Hi Mihir Hardas, thanks for the comment!

@mihirhardas8297 3 года назад

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

@abhinavkumar3232 2 года назад

Hi Doug. Thank you for the video and the help. In Power Query, under Advance Options ( 02:40), I have only one option which says 'No. Of column to split into'. It is not showing any Row option. Please help how to get the row option. Thansk.

@sandunmadushan1732 Год назад

thank you so much

@DougHExcel Год назад

Hi sandun madushan, you're welcome!

@SohailAhmad1 8 месяцев назад

AMAZING.. THATS GREAT

@DougHExcel 8 месяцев назад

Hi @SohailAhmad1, thanks for the comment!

@mbusoharvey2447 3 года назад

@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 3 года назад

like this? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W4sK4V9G0GY.html

@mbusoharvey2447 3 года назад

@@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

@pearlarasbord Год назад

Great thanks

@DougHExcel Год назад

Thanks!

@Pandash1 Год назад

if you are using a Mac, 'from table' on power query is not available.

@miodragzivanovic9922 2 года назад

Thank you :)

@DougHExcel 2 года назад

You're welcome!

@MOATHALSHIBLIvfx 2 года назад

thank you

@DougHExcel 2 года назад

You’re welcome!!!

@damoniery 5 лет назад

In my case the rows option doesn't shows up... The only option that i have only split them into columns

@DougHExcel 4 года назад

might need to update your version of the add-in

@Itz_Jesses_world 3 года назад

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 3 года назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QKRSF7TM88M.html