MacOS – Numbers v3.1: Automatically link URLs in spreadsheet

iworklibreofficemacosnumbers

I've inherited a spreadsheet that has a loooong column (thousands) of URLs, all unclickable. I'd like to make them clickable.

I have tried the following:

  • Highlighting the column.
    Select column, then Edit > Substitutions > Tick 'Smart Links'.
    Doesn't work on the selected column. ONLY works if I edit a single cell's text (the URL).

  • The rich-text dance.
    Pasted the contents of my URL column to a (rich text) Textedit document, and invoked the substitutions magic. Hooray! The formerly text-only URLs are now clickable links in TextEdit! But pasting them back into Numbers doesn't work: They appear as unclickable text again.

  • Weren't there prefs for that…?
    I thought this option was available in previous versions of Numbers: a check-box in Numbers' preferences, "Hit enter to go enter the next cell below to edit that cell's content". I did this in an older Numbers version: While slow, it did the trick: Hit enter (edit the existing text of the highlighted cell), hit return, and boom! Numbers made the previous cell's URL content clickable! Where is this option in Numbers 3.1?

  • Hello, Inspector.
    In Number's cell inspector panel, there's a "Character Styles" drop-down that seemingly provides what I am looking for – "Link"! Except it turns gray if I select a column, or even just highlight a single cell. It is only selectable if I edit a single cell's content.

Edit:
I sorta-kinda made those URLs clickable by installing Libre-Office and using Calc (LO's spreadsheet component). Pasted in the links, and with Calc's smart URL detection, it found the URLs and made them clickable. Saved as an Excel spreadsheet, opened in Numbers, et voilà, clickable URLs.

Surely there's an easier way other than "Download and install Libre-Office"?!

Best Answer

Another way to do this without clicking in each cell would be to use the =HYPERLINK() function.

Here are some steps:

  • Add a second column (Let's call it B)
  • In the adjacent cell, add a formula like this: =HYPERLINK(A1)
  • Drag the formula down

Provided that the original URL's are valid, this will convert them.

You can even add your own text in place of the URL. (see screenshot)

So, given column A has a long list of URL's starting in cell A1, then in B1 enter:

=HYPERLINK(A1,"Clickable Link")

One could even parse the original URL to get just the human readable part using MIDDLE() or similar.

Hope this helps!! Sample Numbers Formula for Hyperlink

Related Question