how to convert numerical values to english words in Numbers

@ Listen Inn,

Nice litte converter. Somewhat simpler than the one I wrote years ago for AppleWorks, due to the different approach taken to building the lookup table and parsing the number.

Extending the range to include larger numbers would, I think, be a fairly easy task. Extending the range to the right of the decimal place would be more difficult—the curent design is a specific one, intended for a specific type of number (currency, with two decimal places), and the spreadsheet currently requires the original number to always include a decimal with two digits following it.

Certainly does what Brad asked, though.

Regards,

Barry

There is an Applescript that can do this conversion, written by Yvan Koenig. You select a cell that has a number in it and then run the script. The resulting text is put into the cell to the right of the number. The script could be edited to fit your exact situation of the number being in A3 and the text going to C2. Scripts are usually written to be as generic (and therefore reusable) as possible.

To test it out,

1. Open the Applescript Editor application
2. Copy/Paste the script from below into the editing window of Applescript Editor
3. Click the Compile button. It should compile with no errors.
4. In a Numbers table, select a cell that has a number in it.
5. In AppleScript Editor, click on the Run button.

To make it available via keyboard shortcut, you can create an Automator service from the script and assign a keyboard shortcut to it.

The script is everything from here on:

(*

Auteur : Hubert Jacquot-Dion (mailto:hubert@jacquotdion.com).

Correction typographique : Yannik Dumont (France)

Version Suisse Romande : Yvan Oberson (mailto:yvan.Oberson@bluewin.ch)

Ce script traduit un nombre (avec au plus deux décimales), par exemple 1275.80 as

"One Thousand Two Hundred and Seventy Five Dollars Heighty Cents"

Script adapté pour Numbers par Yvan KOENIG le 30 août 2007

remanié le 25 février 2009 pour Numbers '09

Minor revisions by Barry Spence May 25, 2010 to correct English, for example 18,285.00 as

"Eighteen Thousand Two Hundred Eighty Five Dollars and Zero Cents"

+++++++++

Enregistrer le script en tant que Script : numberInLetters.scpt

déplacer l'application créée 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 'traduire'.

menu Scripts > Numbers > numberInLetters

La cellule située à la droite de la cellule pointée reçoit la chaîne 'nombre en lettres'.

--=====

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 : numberInLetters.scpt

Move the newly created application 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 contains the number to translate.

menu Scripts > Numbers > numberInLetters

The pointed cell receives the entered string.

The cell on its right in the row under receive the current date_time.

--=====

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)

modifié le 05/09/2007 __ version 2.5.01

modifié le 06/09/2007 __ version 2.5.02

modifié le 25/02/2009 __ version 2.5.03 pour Numbers '09

*)

property Nombre : {"00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 40 50 60 70 80 90"}

property EnLettre : {"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"}

property Maj : "ABCDEFGHIJQLMNOPQRSTUVWXYZ"

property Min : "abcdefghijklmnopqrstuvwxyz"

property Virg : "Dollar"

property ApVirg : "Cent" -- "centimes"

property Annd : "and"

property theApp : "Numbers"

property delim : missing value

property deci : missing value

--=====

on run

if character 2 of (0.5 as text) is "." then

set delim to ","

set deci to "."

else

set delim to ";"

set deci to ","

end if

set {dName, sName, tName, rName} to my getSelection()

if rName is missing value then

if my parleFrancais() then

error "Pas de cellule sélectionnée !"

else

error "No selected cell !"

end if

end if

set twoNames to my decoupe(rName, ":")

set {rowNum1, colNum1} to my decipher(dName, sName, tName, item 1 of twoNames)

tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

set theNumber to value of cellrowNum1 of columncolNum1

end tell -- Numbers

set theNumber to theNumber as text

try

if theNumber contains "E" then

set deb to text 1 thru ((offset of "E" in theNumber) - 1) of theNumber

if deb starts with deci then

set p1 to ""

else

set p1 to text 1 thru ((offset of deci in deb) - 1) of deb

end if

set p2 to text (1 + (offset of deci in deb)) thru -1 of deb

if theNumber contains "+" then

set mul to (text (1 + (offset of "+" in theNumber)) thru -1 of theNumber) as integer

if mul < (count of p2) then

set p1 to p1 & text 1 thru mul of p2

set p2 to text (mul + 1) thru -1 of p2

set theNumber to p1 & deci & p2

else

set theNumber to p1 & p2

end if

else

set mul to (text (1 + (offset of "-" in theNumber)) thru -1 of theNumber) as integer

set p1 to p1 & p2

repeat mul times

set p1 to "0" & p1

end repeat

set theNumber to (character 1 of p1) & deci & text 2 thru -1 of p1

end if

end if

on error

my notAnumber()

end try

set enLettres to my main(theNumber as text)

tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

if (colNum1 + 1) > (countcolumns) then add column after last column

set value of cell rowNum1 of column (colNum1 + 1) to enLettres

end tell

end run

--=====

on main(AEnvoyer)

set AppleScript's text item delimiters to {}

-- filtre d'éventuels caractères espace, espace insécable ou euro

if AEnvoyer contains " " then set AEnvoyer to my supprime(" ", AEnvoyer)

if AEnvoyer contains (ASCII character (202)) then set AEnvoyer to my supprime(ASCII character (202), AEnvoyer)

if AEnvoyer contains (ASCII character (219)) then set AEnvoyer to my supprime(ASCII character (219), AEnvoyer)

-- remplace un point décimal isolé par une virgule décimale

if deci is "," then

if (AEnvoyer does not contain ",") and AEnvoyer contains "." then set AEnvoyer to my remplace(".", AEnvoyer, ",")

end if

try

set _ to AEnvoyer as number

set PosVirg to (offsetofdeciinAEnvoyer)

if PosVirg ≠ 0 then

if last item of AEnvoyer is deci then

if PosVirg = 1 then

set AEnvoyer to "0"

else

set AEnvoyer to items 1 thru -2 of AEnvoyer as text

end if

else if PosVirg ≠ 1 then

set AEnvoyer to (items 1 thru (PosVirg - 1) of AEnvoyer & deci & items (PosVirg + 1) thru -1 of AEnvoyer) as text

else

set AEnvoyer to "0" & deci & (items 2 thru -1 of AEnvoyer as text)

end if

end if

set CestBon to my Traduit(AEnvoyer, Virg, ApVirg)

set AppleScript's text item delimiters to {}

return CestBon

on error

set AppleScript's text item delimiters to {}

my notAnumber()

end try

end main

--=====

on notAnumber()

if my parleFrancais() then

error "La cellule pointée ne contient pas un nombre !"

else

error "The selected cell doesn't contain a number !"

end if

end notAnumber

--=====

on Traduit(ATraduir, Franc, Centime)

local InText, x, AtraduirT, NbcT, tour, AvV, ApV, Tempo, NbLet, MinToMaj, TempoNbLet

set InText to false

set x to 0

set AtraduirT to ATraduir

set NbcT to countitem of AtraduirT-- combien de caractères dans ce nombre

set tour to offsetofdeciinAtraduirT-- tour contient la position de la virgule

if tour = 0 then -- il n'y a pas de virgule

set AvV to AtraduirT-- prend le nombre envoyé pour la partie avant la virgule

set ApV to "0" -- met à vide la partie après la virgule

else -- il y a une virgule

set AvV to (items 1 thru (tour - 1)) of AtraduirT as text-- prend les chiffres avant la virgule

if NbcT - tour = 1 then set AtraduirT to AtraduirT & "0" -- cas d'un chiffre après la virgule

set AtraduirT to text 1 thru 2 of (1 / 2 as text) & (items (tour + 1) thru -1 of AtraduirT) as text

set AtraduirT to AtraduirT * 100

round_truncate(AtraduirT, 0)

set ApV to result

end if

set Tempo to "Zero "

if AvV ≠ "0" then set Tempo to my Conv(AvV)

(**)

if Tempo ≠ "One " and Franc ≠ "virgule" then set Franc to Franc & "s"

set NbLet to Tempo & Franc & " and "

set Tempo to "Zero "

if ApV ≠ "0" then set Tempo to my Conv(ApV)

if Tempo ≠ "One " then set Centime to Centime & "s"

set NbLet to NbLet & Tempo & Centime

set MinToMaj to offset of (item 1 of NbLet) in Min

set TempoNbLet to items of NbLet

set item 1 of TempoNbLet to itemMinToMaj of Maj

set NbLet to TempoNbLet as text

return NbLet

end Traduit

--=====

on Conv(AConv2)

local NbLet, NbC, NbCTour, Position2, z, ATraiter, NbCAt, ATraiter, IlReste, AConv2

set NbLet to ""

set NbC to count item of AConv2

if NbC > 12 then -- élimine les nombres en dessus de mille Milliards moins un centime

if my parleFrancais() then

error "Choisir un nombre en dessous de mille milliards !"

else

error "Choose a number less than One Thousand of Milliards !"

end if

set NbLet to "Zero "

else

set NbCTour to round (NbC / 3) roundingup-- nombre de traitement

repeat with Position2 from 1 to NbCTour

set z to 3

if NbC < 3 then set z to NbC -- traite par 3 chiffres

set ATraiter to (characters -1 thru -z of AConv2) as text

set NbCAt to count item of ATraiter

repeat with z in items of ATraiter

if z as integer ≠ 0 then exit repeat

set NbCAt to NbCAt - 1

end repeat

if NbCAt ≠ 0 then

set ATraiter to (characters -1 thru -NbCAt of ATraiter) as text

my Trouv(ATraiter, Position2)

set NbLet to result & " " & NbLet

end if

set IlReste to count item of AConv2

if IlReste < 4 then set IlReste to 4

set AConv2 to (characters 1 thru (IlReste - 3) of AConv2) as text

set NbC to NbC - 3

end repeat

end if

if last word of NbLet is in {"millions", "million", "milliards", "milliard"} then set NbLet to NbLet & "of "

return NbLet

end Conv

--=====

on Trouv(AConv, Position)

local NbCAt, ATraiter, Connu, Dizaine, Unite, Trouve, Centaine

set NbCAt to count item of AConv

if NbCAt > 1 then

set ATraiter to (items -1 thru -2 of AConv) as text

set Connu to offsetofATraiterinNombre

if Connu = 0 then -- traitement à l'unité

set Dizaine to item 1 of ATraiter & "0"

set Unite to "0" & item 2 of ATraiter as text

set Connu to offsetofDizaineinNombre

set Trouve to item ((Connu + 2) / 3) of EnLettre

set Connu to offsetofUniteinNombre

set Trouve to Trouve & " " & item ((Connu + 2) / 3) of EnLettre

else

set Trouve to item ((Connu + 2) / 3) of EnLettre

end if

if NbCAt = 3 then

set ATraiter to "0" & item 1 of AConv

set Connu to offsetofATraiterinNombre

if Connu > 6 then

set Centaine to item ((Connu + 2) / 3) of EnLettre & " Hundred"

else

set Centaine to "One Hundred" --"cent"

end if

if Trouve = "Zero" then

set Trouve to Centaine

else

--set Trouve to Centaine & " and " & Trouve

set Trouve to Centaine & " " & Trouve

end if

end if

else

set ATraiter to "0" & AConv

set Connu to offsetofATraiterinNombre

if Connu > 6 then

set Trouve to item ((Connu + 2) / 3) of EnLettre

else

set Trouve to "One"

end if

end if

if Trouve = "One" then

if Position = 2 then

set Trouve to "One Thousand"

else if Position = 3 then

set Trouve to "One Million"

else if Position = 4 then

set Trouve to "One Billion" -- "Billion"

end if

else

if Position = 2 then

set Trouve to Trouve & " Thousand"

else if Position = 3 then

set Trouve to Trouve & " Million"

else if Position = 4 then

set Trouve to Trouve & " Billion" -- "Billion"

end if

end if

return Trouve

end Trouv

--=====

on number_to_text(this_number)

local x, y, z, decimal_adjust, |Nombre de chiffre|, first_part, second_part, converted_number, third_part

set this_number to this_number as text

if this_number contains "E+" then

set x to offset of "." in this_number

set y to offset of "+" in this_number

set z to offset of "E" in this_number

set decimal_adjust to characters (y - (length of this_number)) thru -1 of this_number as string as number

set |Nombre de chiffre| to z - (x + 1)

if x is not 0 then

set first_part to characters 1 thru (x - 1) of this_number as string

else

set first_part to ""

end if

set second_part to characters (x + 1) thru (z - 1) of this_number as string

set converted_number to first_part

repeat with i from 1 to decimal_adjust

try

set converted_number to converted_number & characteri of second_part

on error

set converted_number to converted_number & "0"

end try

end repeat

set third_part to characters (decimal_adjust + x + 1) thru (|Nombre de chiffre| + x) of this_number as text

if length of third_part is not 0 then set converted_number to converted_number & "." & third_part

return converted_number

else

return this_number

end if

end number_to_text

--=====

on round_truncate(this_number, decimal_places)

-- NOTE: this sub-routine uses the number_to_text() sub-routine

local rounding_value, mod_value, second_part, first_part

if decimal_places is 0 then

set this_number to this_number + 0.5

return my number_to_text(this_number div 1)

end if

set rounding_value to "5"

repeat decimal_places times

set rounding_value to "0" & rounding_value

end repeat

set rounding_value to (character 2 of (1 / 2 as text) & rounding_value) as number

set this_number to this_number + rounding_value

set mod_value to "1"

repeat decimal_places - 1 times

set mod_value to "0" & mod_value

end repeat

set mod_value to (character 2 of (1 / 2 as text) & mod_value) as number

set second_part to (this_number mod 1) div mod_value

if length of (second_part as text) is less than decimal_places then

repeat decimal_places - (length of (second_part as text)) times

set second_part to ("0" & second_part) as string

end repeat

end if

set first_part to this_number div 1

set first_part to my number_to_text(first_part)

set this_number to (first_part & "." & second_part)

return this_number

end round_truncate

--=====

on supprime(s, t)

local _

set AppleScript's text item delimiters to s

set _ to text items of t

set AppleScript's text item delimiters to ""

set t to _ as text

set AppleScript's text item delimiters to {}

return t

end supprime

--=====

on remplace(s, t, v)

local _

set AppleScript's text item delimiters to s

set _ to text items of t

set AppleScript's text item delimiters to v

if (count of _) < 3 then set t to _ as text

set AppleScript's text item delimiters to {}

return t

end remplace

--=====

(*

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

--=====

on getSelection()

local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection

tell application "Numbers"

activate

tell document 1

set mySelectedRanges to selection range of every table of every sheet

repeat with sheetRanges in mySelectedRanges

repeat with thisRange in sheetRanges

if contents of thisRange is not missing value then

try

thisRange as text

on error errMsg number errNum

set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)

return {myDoc, mySheet, myTable, myRange}

end try

end if -- contents…

end repeat -- thisRange

end repeat -- sheetRanges

end tell -- document 1

end tell -- application

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

--=====

on parleFrancais()

local z

try

tell application theApp to set z to localized string "Cancel"

on error

set z to "Cancel"

end try

return (z = "Annuler")

end parleFrancais

--=====

--[/SCRIPT]{code}

it is not working on numbers 3.1

could you please help me with the same issue, but on numbers 3.1 ??

thanks

it is not working on numbers 3.1

For it to work in Numbers 3.1, replace THIS PART:

on getSelection()

local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet,myDoc, mySelection

tell application "Numbers"

activate

tell document 1

set mySelectedRanges to selection range of every table of every sheet

repeat with sheetRanges in mySelectedRanges

repeat with thisRange in sheetRanges

if contents of thisRange is not missing value then

try

thisRange as text

on error errMsg number errNum

set {_, myRange, _, myTable, _, mySheet,_, myDoc} to my decoupe(errMsg,quote)

return {myDoc, mySheet, myTable,myRange}

end try

end if -- contents…

end repeat -- thisRange

end repeat -- sheetRanges

end tell -- document 1

end tell -- application

return {missing value, missing value, missing value, missing value}

end getSelection

with THIS:

--Use this handler for Numbers 3

on getSelection()

tell application "Numbers"

tell front document

set dName to its name

tell active sheet

set sName to its name

tell (first table whose class of selection range is range)

set tName to its name

tell the selection range

set rName to name of its first cell

end tell

end tell

end tell

end tell

end tell

return {dName, sName, tName, rName}

end getSelection

Note that as written it places the result in the cell immediately to the right of the cell you have selected.

SG

thanks, this helped me a lot and solved my problem

it works now i need to create this automator service you mentioned,

can you please help me with that?

how can i create a shortcut that i can use in numbers?

and is there a way it automatically fills the words when i change the number?

Thanks

spuDaisy:

There is a check writing spreadsheet for Numbers 09 at this web address.

http://iworkcommunity.com/content?page=3

Maybe it will help you.

Listen Inn

The check writer spreadsheet is interesting. Four things though:

1. It is limited to numbers smaller than 100,000
2. After unzipping the download, change the file extension from .nbrtemplate to .numbers so you don't have to add it to your templates. It doesn't need to be a template.
3. If you are interested in how it works, unhide rows 13-101 in the conversion table to see more its inner workings. This will unhide "eleven" "twelve", etc.
4. Once you understand how it works, you can modify it to work with larger numbers if you need it to.