Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Create hyperlink from text in a cell

Hi,

Was just wondering if this is possible. I can't see any way to do this.

I want to have a column of numbers and have it so that if I click on those numbers it sends me to a URL including that number.

E.G. Column displays;
1000
1001
1002

and if clicked on it actually hyperlinks to;
http://sum.url/1000
http://sum.url/1001
http://sum.url/1002
etc.

I presume that the only way to do this is to have a separate column that builds the URL from the first column, but that's not pretty and as a spoilt Apple user I demand pretty! And a pony!

Thanks.

MBP 15" 2.8GHz, Mac OS X (10.6.6)

Posted on Feb 15, 2011 12:58 PM

Reply
Question marked as Best reply

Posted on Feb 15, 2011 1:29 PM

Screaming.Pict wrote:
Hi,

Was just wondering if this is possible. I can't see any way to do this.

I want to have a column of numbers and have it so that if I click on those numbers it sends me to a URL including that number.

E.G. Column displays;
1000
1001
1002

and if clicked on it actually hyperlinks to;
http://sum.url/1000
http://sum.url/1001
http://sum.url/1002
etc.


Download *_iWork Formulas and Functions User Guide_*
and study the function HYPERLINK.

It may be a valid answer to your question, if your Url matches the Numbers requirements.

Yvan KOENIG (VALLAURIS, France) mardi 15 février 2011 22:28:07
10 replies
Question marked as Best reply

Feb 15, 2011 1:29 PM in response to Screaming.Pict

Screaming.Pict wrote:
Hi,

Was just wondering if this is possible. I can't see any way to do this.

I want to have a column of numbers and have it so that if I click on those numbers it sends me to a URL including that number.

E.G. Column displays;
1000
1001
1002

and if clicked on it actually hyperlinks to;
http://sum.url/1000
http://sum.url/1001
http://sum.url/1002
etc.


Download *_iWork Formulas and Functions User Guide_*
and study the function HYPERLINK.

It may be a valid answer to your question, if your Url matches the Numbers requirements.

Yvan KOENIG (VALLAURIS, France) mardi 15 février 2011 22:28:07

Feb 16, 2011 4:25 AM in response to KOENIG Yvan

Yvan,

Thanks for the reply- I should have mentioned that I've already looked at the Hyperlink function- and that will allow me to enter the text in one column and create a hyperlink in another column.

I want to do something rather more clever and have both in the same column- i.e. have some sort of a custom number format so that if I enter '1001' in the field it displays '1001' but also hyperlinks the same field to 'http://random.url/1001' or something like that.

I think I'm probably being overly picky and should just accept having 2 separate columns- one to enter the text and one to produce the hyperlink.

Feb 16, 2011 5:24 AM in response to Screaming.Pict

Insert the numbers in an auxiliary table and put a formula in the main table
or
insert the numbers in column C, put formulas in column D then hide column C.
or
create an AppleScript used to enter the numerical value which will build then insert the wanted link displaying the number.

I have some minutes available so I will build a quick and dirty skeleton doing that.

Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 14:21:45

Feb 16, 2011 6:15 AM in response to KOENIG Yvan

Here is the script.

--

--[SCRIPT calc&_storeHyperlink]
(*
Enregistrer le script en tant que Script, Application ou Progiciel : calc&_storeHyperlink.xxx
déplacer le fichier créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Placez le curseur dans la cellule que vous souhaitez alimenter.
menu Scripts > Numbers > calc&_storeHyperlink
La cellule pointée reçoit la formule calculant l'hyperlien à pointer.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
+++++++
Save the script as Script, Application or Application Bundle: calc&_storeHyperlink.xxx
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Put the cursor in the cell which must receive your entry.
menu Scripts > Numbers > calc&_storeHyperlink
The pointed cell receives the formula calculating the link to point to.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
--=====
Yvan KOENIG (VALLAURIS, France)
2011/02/16
*)
property theApp : "Numbers"
property link_root : "http://sum.url/"
--=====
on run

set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

tell application (path to frontmost application as string)
if my parleAnglais() then
set link_variable to text returned of (display dialog "Variable link’s component…" default answer "blabla bla")
else
set link_variable to text returned of (display dialog "Composant variable du lien…" default answer "blabla bla")
end if
end tell
my doYourDuty(dName, sName, tName, rowNum1, colNum1, link_variable)

end run
--=====
on doYourDuty(d, s, t, r, c, link_num) (*
d = document's name
s = sheet's name
t = table's name
r = rowIndex
c = columnIndex
link_num = variable link component *)
local HYPERLINK_loc, delim_loc
set HYPERLINK_loc to my getLocalizedFunctionName("Numbers", "HYPERLINK")
set delim_loc to my getLocalizedDelimiter()
tell application "Numbers" to tell document d to tell sheet s to tell table t
set value of cell r of column c to "=" & HYPERLINK_loc & "(" & quote & link_root & link_num & quote & delim_loc & quote & link_num & quote & ")"
end tell -- application …
end doYourDuty
--=====
(*
set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
*)
on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
local two_Names, row_Num1, col_Num1, row_Num2, col_Num2
tell application "Numbers"
set d_Name to name of document d_Name (* useful if we passed a number *)
tell document d_Name
set s_Name to name of sheet s_Name (* useful if we passed a number *)
tell sheet s_Name
set t_Name to name of table t_Name (* useful if we passed a number *)
end tell -- sheet
end tell -- document
end tell -- Numbers
if r_Name contains ":" then
set two_Names to my decoupe(r_Name, ":")
set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
if item 2 of two_Names = item 1 of two_Names then
set {row_Num2, col_Num2} to {row_Num1, col_Num1}
else
set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
end if
else
set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, r_Name)
set {row_Num2, col_Num2} to {row_Num1, col_Num1}
end if -- r_Name contains…
return {row_Num1, col_Num1, row_Num2, col_Num2}
end getCellsAddresses
--=====
(*
set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
*)
on getSelParams()
local r_Name, t_Name, s_Name, d_Name
set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

if r_Name is missing value then
if my parleAnglais() then
error "No selected cells"
else
error "Il n'y a pas de cellule sélectionnée !"
end if
end if

return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
end getSelParams
--=====
(*
set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
apply to named row or named column !
*)
on decipher(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t to ¬
return {address of row of cell n, address of column of cell n}
end decipher
--=====
(*
set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
*)
on getSelection()
local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

tell application "Numbers" to tell document 1
repeat with i from 1 to the count of sheets
tell sheet i
set x to the count of tables
if x > 0 then
repeat with y from 1 to x
try
(selection range of table y) as text
on error errMsg number errNum
set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
return {theDoc, theSheet, theTable, theRange}
end try
end repeat -- y
end if -- x>0
end tell -- sheet
end repeat -- i
end tell -- document
return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on decoupe(t, d)
local l
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to ""
return l
end decoupe
--=====
(*
Set the parameter delimiter which must be used in Numbers formulas
*)
on getLocalizedDelimiter()
if character 2 of (0.5 as text) is "." then
return ","
else
return ";"
end if
end getLocalizedDelimiter
--=====
on get_iWorkNum(a)
local verNum
tell application a to set verNum to item 1 of my decoupe(get version, ".")
if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
return "09"
else
return "11"
end if
end get_iWorkNum
--=====
(*
Useful to get function's localized name if we need to build formulas
examples:
set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
*)
on getLocalizedFunctionName(theApp, x)
return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
end getLocalizedFunctionName
--=====
on getLocalizedName(a, x, f)
tell application a to return localized string x from table "Localizable" in bundle file f
end getLocalizedName
--=====
on parleAnglais()
local z
try
tell application theApp to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z is not "Annuler")
end parleAnglais
--=====
--[/SCRIPT]
--


Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 15:15:08

Feb 16, 2011 6:21 AM in response to Screaming.Pict

You can definitely create a format that turns it into a URL. It does not turn it into a hyperlink, though. I tried it just to be sure and it does not work. EDIT: I see Yvan has just posted a script.

As an alternative, you could use a script. You select a cell and run the script (which could be done with a keyboard shortcut). The script turns it into a URL and opens your browser.

I recommend using two columns, one with your text and one with the HYPERLINK function that converts it to a URL. You can resize the HYPERLINK column so the cell size is square and format it so it is a solid color or has an image of a button in it and the text is not visible.

Message was edited by: Badunit

Feb 16, 2011 6:59 AM in response to Badunit

Hello Badunit

The HYPERLINK function give us the ability to display exactly what we want in the cell.
So, my script ask the user to enter the number (or anything else)
then it build and insert a formula designed to display only the entered value.

User uploaded file

In the screenshot you see the French formula.

The English one is :
=HYPERLINK("http://sum.url/1234","1234")

If the OP dislike the scheme using a dialog belonging to the script to enter the value,
here is an alternate version which requires that he enter the value in the cell then selecte it and trigger the script.

--

--[SCRIPT calc&_store_Hyperlinkalt]
(*
Enregistrer le script en tant que Script, Application ou Progiciel : calc&_storeHyperlink.xxx
déplacer le fichier créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Placez le curseur dans la cellule contenant la valeur à utiliser pour construire la formule LIEN.HYPERTEXTE.
menu Scripts > Numbers > calc&_storeHyperlink
La cellule pointée reçoit la formule requise.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
+++++++
Save the script as Script, Application or Application Bundle: calc&_storeHyperlink.xxx
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Put the cursor in the cell which store the value which must be used to build the HYPERLINK formula.
menu Scripts > Numbers > calc&_storeHyperlink
The pointed cell receives the formula calculating the link to point to.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
--=====
Yvan KOENIG (VALLAURIS, France)
2011/02/16
*)
property theApp : "Numbers"
property link_root : "http://sum.url/"
--=====
on run

set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

my doYourDuty(dName, sName, tName, rowNum1, colNum1)

end run
--=====
on doYourDuty(d, s, t, r, c) (*
d = document's name
s = sheet's name
t = table's name
r = rowIndex
c = columnIndex*)
local HYPERLINK_loc, delim_loc, link_num
set HYPERLINK_loc to my getLocalizedFunctionName("Numbers", "HYPERLINK")
set delim_loc to my getLocalizedDelimiter()
tell application "Numbers" to tell document d to tell sheet s to tell table t
set link_num to value of cell r of column c as integer
set value of cell r of column c to "=" & HYPERLINK_loc & "(" & quote & link_root & link_num & quote & delim_loc & quote & link_num & quote & ")"
end tell -- application …
end doYourDuty
--=====
(*
set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
*)
on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
local two_Names, row_Num1, col_Num1, row_Num2, col_Num2
tell application "Numbers"
set d_Name to name of document d_Name (* useful if we passed a number *)
tell document d_Name
set s_Name to name of sheet s_Name (* useful if we passed a number *)
tell sheet s_Name
set t_Name to name of table t_Name (* useful if we passed a number *)
end tell -- sheet
end tell -- document
end tell -- Numbers
if r_Name contains ":" then
set two_Names to my decoupe(r_Name, ":")
set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
if item 2 of two_Names = item 1 of two_Names then
set {row_Num2, col_Num2} to {row_Num1, col_Num1}
else
set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
end if
else
set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, r_Name)
set {row_Num2, col_Num2} to {row_Num1, col_Num1}
end if -- r_Name contains…
return {row_Num1, col_Num1, row_Num2, col_Num2}
end getCellsAddresses
--=====
(*
set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
*)
on getSelParams()
local r_Name, t_Name, s_Name, d_Name
set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

if r_Name is missing value then
if my parleAnglais() then
error "No selected cells"
else
error "Il n'y a pas de cellule sélectionnée !"
end if
end if

return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
end getSelParams
--=====
(*
set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
apply to named row or named column !
*)
on decipher(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t to ¬
return {address of row of cell n, address of column of cell n}
end decipher
--=====
(*
set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
*)
on getSelection()
local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

tell application "Numbers" to tell document 1
repeat with i from 1 to the count of sheets
tell sheet i
set x to the count of tables
if x > 0 then
repeat with y from 1 to x
try
(selection range of table y) as text
on error errMsg number errNum
set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
return {theDoc, theSheet, theTable, theRange}
end try
end repeat -- y
end if -- x>0
end tell -- sheet
end repeat -- i
end tell -- document
return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on decoupe(t, d)
local l
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to ""
return l
end decoupe
--=====
(*
Set the parameter delimiter which must be used in Numbers formulas
*)
on getLocalizedDelimiter()
if character 2 of (0.5 as text) is "." then
return ","
else
return ";"
end if
end getLocalizedDelimiter
--=====
on get_iWorkNum(a)
local verNum
tell application a to set verNum to item 1 of my decoupe(get version, ".")
if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
return "09"
else
return "11"
end if
end get_iWorkNum
--=====
(*
Useful to get function's localized name if we need to build formulas
examples:
set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
*)
on getLocalizedFunctionName(theApp, x)
return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
end getLocalizedFunctionName
--=====
on getLocalizedName(a, x, f)
tell application a to return localized string x from table "Localizable" in bundle file f
end getLocalizedName
--=====
on parleAnglais()
local z
try
tell application theApp to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z is not "Annuler")
end parleAnglais
--=====
--[/SCRIPT]
--


Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 15:59:13

Create hyperlink from text in a cell

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.