Numbers: how to get URL from cell containing a link?

TimCoin wrote:

Cell A contains text with a link (Example text), now I would like to have the cell next to it with only the link (specifically just after/including HTTP*). What formula do I need for this?

For example:

Cell A contains: Example text

Then only collect the URL: http://www.apple.com in Cell B.

Hi Tim,

As far as I can tell the link itself is not accessible via formula. And it's not all that easy to access manually either. You have to click the cell, select all the text within it, and then Format > Edit Link (or command-k) :

However I did manage to cobble together an automated way via this Extract Urls from Copied Cells Automator Service (Dropbox download).

To install, just double-click the downloaded .workflow package and, if necessary, click Install Anyway in System Preferences > Security & Privacy.

To use:

1. Select the cell(s) with the hyperlinks.
2. Command-c to copy to the system clipboard
3. Choose Extract Urls from Copied Cells from the Numbers > Services menu.
4. Click once in the top destination cell.
5. Command-v to paste the urls in the destination column.

So starting with this:

I selected cells A2:A4, typed command-c to copy, ran the service from the Numbers > Service menu, clicked once in B2, and typed command-v. That resulted in this:

Let me know if you have any luck with this. Strictly optional (you can just click and choose from the menu if you don't want to get into any of this) but if you are curious to see how the Service does what it does, you can open it in Automator. To do that, in Finder hold down the option key and navigate Go > Library > Services and double-click it. It will look like this:

It takes what you copied to the clipboard when selecting cells and typing command-c, converts it from "rich text" format to html using textutil (built into every Mac), then uses a built-in Automator action to extract the URLs from the html text, formats that so that Numbers can accept the result, and places the result on the clipboard so that it can be pasted into Numbers wherever you want it using command-v.

SG

Here's a slightly more convenient service: Extract Urls from Selected Cells (Dropbox download).

It's the same as above but adds a short script that eliminates the necessity to type command-c to copy. To use:

1. Select the cell(s) with the hyperlinks.
2. Choose 'Extract Urls from Selected Cells' in the Numbers > Services menu.
3. Click once in the top destination cell.
4. Type Command-v to paste the urls in the destination column.

SG

Not sure how this might behave with several thousand rows, but the script below may perform better for you than the Automator Service. For coding simplicity the script assumes there is a link in every cell in the selection you copied to the clipboard. (It might need some modification if some of your selected cells are blank and you want to paste the urls in a column adjacent to the column with the live links and have the values in the rows line up correctly.)

1. Copy-paste script below into Script Editor.
2. Select the cells containing links, and command c to copy to the clipboard.
3. Click the triangle 'run' button in Script Editor.
4. Click once in a Numbers cell and command-v to paste.

(Make sure Script Editor.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility.)

SG

-- select cells, command-c, run script, click cell 1x, command-v to paste urls

set the clipboard to (the clipboardas «class RTF »)

set theHTML to do shell script "pbpaste -Prefer rtf | textutil -convert html -stdin -stdout"

set htmlParts to splitText(theHTML, "<a href=\"http://")

set pasteStr to ""

repeat with i from 2 to (htmlParts's length)

set pasteStr to pasteStr & "http://" & htmlParts'sitemi's first word & return

end repeat

set the clipboard topasteStr'stext 1 thru -2

return pasteStr

to splitText(t, d)

set text item delimiters to d

t'stext items

end splitText

This one still works here: Extract Urls from Copied Cells Automator Service (Dropbox download). Select cells, command-c to copy, then run and paste.

The other one (Extract Urls from Selected Cells) throws off the error you report when run from the Services Menu, though you can open it in Automator and run it from there. Not sure why. But suggest just using the first one.

SG

The Extract Urls from Copied Cells worked on a small number of cells but became unstable as I increased the quantity. I'm working on a list of several thousand records. Now it doesn't work at all even after rebooting. Sometimes I get an error message like "The action “Run AppleScript” encountered an error." Sometimes it pastes whatever was last copied. If the quantity of cells is small, it sometimes will give me the Url with weird suffixes that got onto the clipboard. I'm on El Capitan v 10.11.5, Numbers v. 3.6.2.

Hi Tim,

Perhaps this will work for you. The function PLAINTEXT

Formula in B2 (and Fill Down)

=PLAINTEXT(A2)

Regards,

Ian.

Glad to hear it worked! Thanks for the green tick.

SG

Does this still work? I just tried it on OS X 10.11.4 using Numbers 3.6.2 and when I select the service, I get the following error:

The action “Run AppleScript” encountered an error.

Any ideas? Thanks.