MacBook, Mac OS X (10.6.2)
Porsche+Fender wrote:
Odd question, but is there a way to remove all non-number characters from a column of cells? For example, if I have:
6dfasfads
12Randomletters.
Is there a way to get rid of the letters?
Barry's response is a clever one. I'm a bit bored to miss this track.
--[SCRIPT cleaner]
(*
Enregistrer le script en tant que Script : cleaner.scpt
déplacer le fichier ainsi 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.
Sélectionner le groupe de cellules dont le contenu doit être nettoyé
Aller au menu Scripts , choisir Numbers puis choisir cleaner
Les cellules dont le contenu renferme des caractères non-numériques seront débarassées de ceux-ci.
--=====
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 a Script: cleaner.scpt
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.
Select the range of cells whose content must be cleaned.
Go to the Scripts Menu, choose Numbers, then choose "cleaner"
The cells whose content embed non numerical characters will drop them.
--=====
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)
2010/07/09
*)
--=====
property alloweds : "0123456789.,-"
on run
set {dName, sname, tname, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
tell application "Numbers" to tell document dName to tell sheet sname to tell table tname
repeat with c from colNum1 to colNum2
tell column c
repeat with r from rowNum1 to rowNum2
set maybe to value of cell r
try
maybe * 1
on error (*
The cell doesn't contain a true number so it must be cleaned
*)
set clean to {}
repeat with k in maybe
set k to k as text
if k is in alloweds then copy k to end of clean
end repeat
set value of cell r to my recolle(clean, "")
end try
end repeat -- r
end tell -- column c
end repeat -- c
end tell --Numbers
end run
--=====
(*
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 parleAnglais()
local z
try
tell application "Numbers" to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z is not "Annuler")
end parleAnglais
--=====
on decoupe(t, d)
local oTIDs, l
set oTIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to oTIDs
return l
end decoupe
--=====
on recolle(l, d)
local oTIDs, l
set oTIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to d
set t to l as text
set AppleScript's text item delimiters to oTIDs
return t
end recolle
--=====
--[/SCRIPT]
--Porsche+Fender wrote:
I have two sheets appropriately named (Main and Conversion like in your example).On the "Conversion" sheet, nothing is showing up and on the "Main" sheet, there is a red error triangle...sorry I can't really think of any more details, bur do you know what is going wrong? I copy-pasted the formulas you put in, the only change was to change B2 to D2, where the cell I want to modify is.
Also, how do you get the top labels (ie letters for columns and numbers for rows) to not disappear when the table is not selected?
p.s. where do I find info on this in the excel menu, I looked, but could not find it....
Porsche+Fender wrote:
Thanks, for the help.
Yvan, thanks for your solution, but as I am not experienced with Scripts, I will use Barry's solution.
Barry wrote:
Porsche+Fender wrote:
p.s. where do I find info on this in the excel menu, I looked, but could not find it....
There's no "Excel" menu in Numbers. If you're asking about MS Excel, that would be a question for an MS Excel forum. An internet search for "mactopia excel" (without the quotes) will put you on a pretty direct track to the MS Office and MS Excel 'newsgroups.'
Regards,
Barry
Barry wrote:
PS: Details regarding the functions used are available in the iWork Formulas and Functions User Guide. See the Help menu in Numbers to download your copy.
B
KOENIG Yvan wrote:
Porsche+Fender wrote:
Thanks, for the help.
Yvan, thanks for your solution, but as I am not experienced with Scripts, I will use Barry's solution.
Using a script is as simple that triggering a menu item ** you are free to do as you want.
The interest of a script for this kind of cleaning task is that it doesn't add formulas in the table.
More formulas means smaller speed.
Yvan KOENIG (VALLAURIS, France) lundi 12 juillet 2010 11:22:59
Porsche+Fender wrote:
Barry wrote:
PS: Details regarding the functions used are available in the iWork Formulas and Functions User Guide. See the Help menu in Numbers to download your copy.
B
And I couldn't find it in the Numbers Help menu.
That makes sense that a script that a script would reduce the time that something would take to run. I just don't have experience with them, though I would like to learn one day.
Remove all text/non-number characters