|This week wet room floors were cast in cement
||We received the rest of the tiles
Export YouTube Music Playlist to Excel using Word
Today I write a little different blog post than you are perhaps used to. About a month ago, YouTube announced they would start to demote climate change deniers from the platform. This decision by YouTube lifted one barrier I had in putting my money in their platform. Hence I got a YouTube premium subscription. Since then, I have used YouTube to watch ad-free YouTube movies, listen to music. I created a playlist of Swedish Folk Music. It has become a vast list. I wanted to have it in Excel to manipulate it, but that feature was missing from YouTube. I am sure they will include that, but I have made it myself until YouTube had that feature built in.
How do you export the playlist from YouTube Music into Excel?
Here I created a solution to this based on using Microsoft Word and Microsoft Excel. No programming is involved. Actually, it is more like manual scraping and search and replace transformation. But, hey, it works! A little disclaimer: I got it working for my list, November 2021.
Start by copying the list from music.youtube.com. Make sure you only copy the list and not the menu at the beginning and not the bottom bar with the play controls. Paste the list into Microsoft Word:
Show hidden characters, Ctrl + *. That way, you can see the non-breaking space between the time and the band as a small circle. We will remove that non-breaking space first.
Enter this search criterion:
Replace all. The result has no line with only the non-breaking space. It looks like this:
Now we will put a unique character in front of the image instead of the paragraph mark. You can go to the Insert tab. In the Symbol dialog, you can select an excellent-looking symbol, for example, the sun symbol ☼. Insert it and copy it to the clipboard (other symbols also work if it has not been used in the playlist in an album title etc.). We will use that special symbol in the subsequent transformations. You can also copy the sun symbol from here.
The part with ^0013 finds the paragraph mark. The parenthesis with the forward-slash finds the image and puts it in the search memory. In the replacement, enter a sun character followed by the original image.
This worked beautifully except for the very last time notation. We can insert a sun symbol manually there.
Now we will replace all remaining paragraph marks with a tab character.
This looks messy, but you don’t need to worry. Now we replace the sun character back with a paragraph character.
Now it looks much better already:
The time notation is written in minutes and seconds. Excel will not understand this; we need to add an empty hour zero (plus a colon) in front of the song length.
Now the list looks like this:
Now we will insert a tab before the hyperlink of the album name (that is the link with "browse"). To do that, you need to turn on the display of the “secret” field codes. Press Alt + F9. Don’t worry, this is all internal things. We have made technical things unhidden so that we can manipulate one of the hyperlinks.
Please note that the wildcard is turned off here. That code ^& is the first time you see it used in Word, ever (No?). It will produce the result of the search into the replacement. We prepend it with a tab character.
After the replacement is done, you turn off the display of field codes, and then you will see this result. Alt + F9.
Copy all the text and paste it into a blank Excel document. Here I added the header manually.
Personally, I did not do anything with the images, so I removed that column, but that is up to you.
I was born 1967 in Stockholm, Sweden. I grew up in the small village Vågdalen in north Sweden. 1989 I moved to Umeå to study Computer Science at University of Umeå. 1995 I moved to the Netherlands where I live in Almere not far from Amsterdam.
Here on this site I let you see my creations.
I create, that is my hobby.