Excel Question: how do I paste some hyperlinks?
November 21, 2016 10:00 AM Subscribe
This seems like a simple question, but I can't figure out the answer. How do I paste a bunch of hyperlinks?
Please see the screenshot I've posted. As you'll see, I have a column of text. Each row of text is actually a hyperlink.
I don't need the text, but I need the URLs. How do I paste that group of rows as URLs?
Currently, I'm going cell by cell, right-clicking "Edit Hyperlink," and then manually copying and pasting. This is rather tedious, so I'd like to know how to do it all at once (if possible).
Any ideas?
Please see the screenshot I've posted. As you'll see, I have a column of text. Each row of text is actually a hyperlink.
I don't need the text, but I need the URLs. How do I paste that group of rows as URLs?
Currently, I'm going cell by cell, right-clicking "Edit Hyperlink," and then manually copying and pasting. This is rather tedious, so I'd like to know how to do it all at once (if possible).
Any ideas?
If you have access to the source code for those links, you could copy that and then use text to columns to separate out the part that isn't a URL.
posted by willnot at 10:16 AM on November 21, 2016
posted by willnot at 10:16 AM on November 21, 2016
Response by poster: Willnot -- Unfortunately, I don't have access to the source code. These are pasted in from LinkedIn's built-in analytics stack.
posted by zooropa at 10:18 AM on November 21, 2016
posted by zooropa at 10:18 AM on November 21, 2016
There are websites that will change rich text to HTML; could you copy/paste into one of those then repaste the HTML into Excel and use text to columns with < and > as the delimiters to get just the URLs?
posted by Mrs. Pterodactyl at 10:43 AM on November 21, 2016
posted by Mrs. Pterodactyl at 10:43 AM on November 21, 2016
I thought along the lines of Mrs. Pterodactyl. If you export the Excel sheet as an HTML file, it will keep the URLs (if the test document I just whipped up is any indication) within the HTML code right after a href="
It might be slightly faster, then, to open that exported HTML file in a plain-text editor and Ctrl-F for "a href", deleting everything before the first link, and everything between the end of each link and the next "a href".
If we're talking about a huge list of links, you could try doing a Find-Replace in the HTML file for all the tr and td stuff and replacing it with nothing, but it may not catch everything the first time through. That's probably the method I'd use, sloppy though it may be :)
posted by CheesesOfBrazil at 11:22 AM on November 21, 2016
It might be slightly faster, then, to open that exported HTML file in a plain-text editor and Ctrl-F for "a href", deleting everything before the first link, and everything between the end of each link and the next "a href".
If we're talking about a huge list of links, you could try doing a Find-Replace in the HTML file for all the tr and td stuff and replacing it with nothing, but it may not catch everything the first time through. That's probably the method I'd use, sloppy though it may be :)
posted by CheesesOfBrazil at 11:22 AM on November 21, 2016
Here's something else that should work. I've only tested it with simple URLs though.
1. Get the URL for the top link in your list, and paste it in the cell immediately to the right of where you copied it from. (So if the link is in cell A1, paste the plain text URL into cell B1.)
2. Click once on cell B1. Home tab > click Fill > Flash Fill. Flash fill will look for patterns in the cells in column A that match the example in cell B1, and paste the results into column B.
One my test sheet with 3 simple links, this worked fine.
Since flash fill relies on a pattern in your URLs, it probably won't work for all the items in your list. (Especially if some links are completely formatted, like "http://ask.metafilter.com/" and others are less complete, like "ask.metafilter.com".) But it should get enough to make it worth your while. Once you get some URLs extracted, move those out of the list, and repeat with the new URL at the top. Eventually you'll get most of them, and then the few remaining oddball ones you can just do by hand.
posted by SuperSquirrel at 8:24 AM on November 22, 2016
1. Get the URL for the top link in your list, and paste it in the cell immediately to the right of where you copied it from. (So if the link is in cell A1, paste the plain text URL into cell B1.)
2. Click once on cell B1. Home tab > click Fill > Flash Fill. Flash fill will look for patterns in the cells in column A that match the example in cell B1, and paste the results into column B.
One my test sheet with 3 simple links, this worked fine.
Since flash fill relies on a pattern in your URLs, it probably won't work for all the items in your list. (Especially if some links are completely formatted, like "http://ask.metafilter.com/" and others are less complete, like "ask.metafilter.com".) But it should get enough to make it worth your while. Once you get some URLs extracted, move those out of the list, and repeat with the new URL at the top. Eventually you'll get most of them, and then the few remaining oddball ones you can just do by hand.
posted by SuperSquirrel at 8:24 AM on November 22, 2016
This thread is closed to new comments.
posted by soelo at 10:13 AM on November 21, 2016