Convert numbers into text
I would like to convert numbers into text, in iWork Numbers App. For example, convert “123” to “one hundred twenty-three”. Can someone help me? Thank you very much! 🙏🏻
iMac
I would like to convert numbers into text, in iWork Numbers App. For example, convert “123” to “one hundred twenty-three”. Can someone help me? Thank you very much! 🙏🏻
iMac
Two examples of how it can be done within Numbers are below. The first is good up to 999 and 99/100. The second has a much wider range and can be more easily expanded. I haven't looked at either in a long time. It is possible they can be done better (or totally differently).
C2=TRUNC(B2,−2)
D2=TRUNC(B2,0)−C2
E2=TRUNC(D2,−1)
F2=D2−E2
G2=ROUND((B2−C2−D2)×100,0)
H2=IF(C2>0,LOOKUP(C2,Table 2::A,Table 2::B)&" ","")&IF(E2>19,LOOKUP(E2,Table 2::A,Table 2::B)&" "&LOOKUP(F2,Table 2::A,Table 2::B),LOOKUP(D2,Table 2::A,Table 2::B))&IF(SUM(C2,D2,F2)>0," and ","")&G2&"/100"
The formulas would have to be modified if needed to use it on larger numbers.
The second method is good over the range -999,999,999 and 99/100 to +999,999,999 and 99/100
Column A of Table 2 has the text for all integer numbers up to 999 as text (not formulas). Columns B and C continue on as you see them in the screenshot. You can create the text in column A by using the first method, modified so it does not append "and 00/100" to the numbers, then Paste the formula results to column A.
Table 1::B2 =TRIM(IF(A<0,"Minus ","")&INDEX(Table 2::$A,TRUNC(ABS(A),−6)×10^−6+1)&" "&INDEX(Table 2::$B,TRUNC(ABS(A),−6)×10^−6+1)&" "&""&INDEX(Table 2::$A,MOD(TRUNC(ABS(A),−3),10^6)×10^−3+1)&" "&INDEX(Table 2::$C,MOD(TRUNC(ABS(A),−3),10^6)×10^−3+1)&" "&" "&INDEX(Table 2::$A,MOD(TRUNC(ABS(A),0),10^3)+1)&""&" "&IF(ABS(A)≥1," and ","")&IF(ABS(A)≠0,RIGHT("00"&MOD(ROUND(ABS(A),2),1)×10^+2,2)&"/100",""))
The formula has a pattern to it. It is easy to expand the range with a simple copy/paste and changing some numbers and adding new columns to Table 2 for billions, trillions, etc..
Since Numbers does not provide a function to enable a word expansion of a number, it can be done outside of Numbers via AppleScript, the number name expansion can occur on a dialog, and copy/pasted from there back to Numbers. The following AppleScript/Objective-C example expects Numbers is open to Sheet 1 and a single cell containing a number is selected in table 1. If that is satisfied, then run the script and it produces the following dialog for a cell containing the value 4257:
The text is selectable should you wish to copy/paste elsewhere.
I chose not to replace that original cell content with the word expansion due to the variability of the required cell width from the results. Also, larger number word expansion will simply wrap on these fixed width AppleScript display dialogs, and ideally, were it not for considerably more code, I could have made that second line scrollable and selectable.
Launch the Script Editor from /Applications/Utilities and then copy/paste the following AppleScript code into it. Ideally, the code will output in the localized language. Click the compile (hammer) toolbar button to convert the purple text to multiple colors, and with a cell selected in Numbers, run▷the script.
Code:
-- Reference: https://discussions.apple.com/thread/254466961
-- Reference: Rosettacode Number Names, Objective-C example: https://rosettacode.org/wiki/Number_names#Objective-C
use framework "Foundation"
use AppleScript version "2.4" -- Yosemite or later
use scripting additions
property NSNumberFormatter : a reference to current application's NSNumberFormatter
property NSNumberFormatterSpellOutStyle : a reference to current application's NSNumberFormatterSpellOutStyle
property NSNumber : a reference to current application's NSNumber
tell application "Numbers"
activate
tell front document
tell active sheet's first table
-- the return value is a list item so we flatten the list as text
set cval to (formatted value of cell in selection range) as text
end tell
try
set resultTxt to my number2words(cval) as text
on error errmsg number errnbr
display dialog "[ " & errnbr & " ]: " & errmsg with title "Error"
return
end try
display dialog "Number: " & cval & return & return & resultTxt with title "Number to Spelled Out Text"
end tell
end tell
return
on number2words(n)
-- return the spelled out number string
set val to NSNumber's alloc()'s initWithLongLong:n
return (NSNumberFormatter's localizedStringFromNumber:val numberStyle:NSNumberFormatterSpellOutStyle) as text
end number2words
Here's a Shortcut (run via the built-in Shortcuts app) that converts a value copied to the clipboard to words, then places the result on the clipboard for pasting wherever.
You can inspect it here
https://www.icloud.com/shortcuts/979d5a06172e46eb87345150ab7e6f50
and download it if you wish to use it.
It contains a short AppleScript (actually AppleScriptObjC) and looks like this (it's already functioning so you don't have to recreate this):
Some of the code is similar to VikingOSX above, but more concise, and it handles copy-pasting.
To use it, select a Numbers cell with a number, type command-c to copy, run, select a cell where you want the result, and command-v to paste. For simplicity it contains no error-checking, and will give a strange message if you don't first select a cell with a number and command-c to copy to the clipboard.
You can use it with other apps as well.
As usual, for easy access you can pin the Shortcut in the Menu Bar, etc. Or just run it from the Shortcuts app.
Results from some testing:
SG
There is no easy function for this ;-(
For what range of numbers would you need this, 0-999 or higher?
The higher the maximum number will be the more steps would be needed.
👏 Nice adaptation.
Convert numbers into text