Created a Comma Separated Value List from a One Column Table or List
Sometimes we have a proper table or list of data but want to convert it to a delimited list (i.e. separated by some delimiter like commas or semicolons). If it is just a one column table it's not that hard to do this with transpose and then some concatenation. However if it's a multiple column table it become a bit more involved...unless you have Power Query. Now PQ makes it much easier to do this with a couple steps and some small samples of M code. See the video to check it out.
ENTER DESCRIPTION HERE
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 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.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 18
Power Query to the rescue! Thanks Doug : )
@DougHExcel
6 жыл бұрын
Thanks Mike!
WOW!!!! So cool. I didn't know this strategy.
@DougHExcel
4 жыл бұрын
PQ is so useful!
É incrível o que o Power Query pode fazer, sensacional essa ferramenta, Obrigado Doug por mais esse vídeo..Obrigado.
@DougHExcel
6 жыл бұрын
Muito obrigado!
Great presentation! if I want to compare an item to this list, do I need to add a comma in the beginning and at the end so that I could do an exact match ? ex: ',Item14,' in ,Item3,Item7,Item14,Item20, How can I add the comma at the beginning and at the end? is it necessary?
@DougHExcel
3 жыл бұрын
maybe one of these could help: kzread.info/dash/bejne/rGGJ1auJY7GalMo.html kzread.info/dash/bejne/ZqBtmaZpcZXdedo.html kzread.info/dash/bejne/rGGJ1auJY7GalMo.html
Hi Doug, Would it be possible to have exercise files with the videos?
@DougHExcel
6 жыл бұрын
sorry no files available ☹
Hi Doug H How about revert this step? I have Comma Separated Value List in one column and want to create multiple row Thanks
@DougHExcel
2 жыл бұрын
maybe these? kzread.info/dash/bejne/Yq5_mderd67Nj7Q.html kzread.info/dash/bejne/gI6Xy8aPnc6dcs4.html
it doesnt give me the option to extract 3:41 ....only to expand. help.
@DougHExcel
2 жыл бұрын
Depends on the version of Excel/power query add on (if applicable)
so informative knowledge you delivered but i saw there is a private videos it is not good Sir if your aim is delivering knowledge why you hide the another videos.Sir if you distribute knowledge Allah will give you those knowledge which you are not known.Jazakallah.
@DougHExcel
5 жыл бұрын
Thanks for the comment!
Would it be possible to do this simultaneously to multiple rows and create multiple cells with their own comma separated list? I work with a data set that identifies Keywords in a text block and assigns them each a Category and Subcategory. Imagine that an entire article of Sports Illustrated was analyzed like this, I would find Keywords like Lebron James, Adam Silver, Cal Ripken, Buck Showalter, etc. and assign them broad categories like NBA or MLB, etc. and Subcategories for NBA Athletes, NBA Owners, MLB Players, MLB Owners. I work with one client that might only want to see the NBA topic and another only want the MLB topic, but I track them all in one spreadsheet to see larger sports-wide trends. I can split lists in cells in the Topic, Subtopic, and Keyword column each into their own rows (kzread.info/dash/bejne/gI6Xy8aPnc6dcs4.html) to then select the topics they want. Now I want to take these relevant Categories, Subcategories, and Keywords and put them each back into their own column per client based on the unique article link/URL I found these names mentioned. I imagine I could follow this process three times for each column and manually sort them, but is it possible to do all three at once? Thank you, your videos are super useful!
@DougHExcel
2 жыл бұрын
maybe grouping and then pulling it out? kzread.info/dash/bejne/l55qkrmxkdjTj6g.html kzread.info/dash/bejne/fa6iss-rqLC0iaw.html