Formula for Number to Text

jowan_qupty · 2018-12-05T20:13:05+00:00

Hi all, I would like to create a formula field (or any other way) that takes a number field and “translates” it to a text. ex: 1053 --> One thousand and fifty three. any idea on how to achieve this?

J

+18

Jeremy_Oglesby
Inspiring
1691 replies
6 years ago
December 5, 2018

There’s not an easy way to do this. I’m sure it’s possible with a gigantic nested conditional formula, but that’s going to be a pain to write. If you’re interested in trying, you’ll probably be making use of:

SUBSTITUTE({Field with Number}, "1", "one ")  <-- For each digit 1-9

I would have 10 different fields for this, each one taking the output of the one before it and converting its assigned digit to the word representing that digit.

Next, you’d probably want to figure out the base10 thousands/hundreds language needed with conditional logic, and apply those words in the right place, with statements something like this:

IF({Field with Number} > 999, REPLACE({Output of last digit replace field}, FIND(" ",{Output of last digit replace field}),1," thousand "))

You’ll be left with 0's still in there, so you’ll need conditional statements to figure out where the 0's are in relation to other digits in the original number to determine the base10 tens to convert (if five is followed by a 0, and the 0 is in the last place, convert the five and 0 to fifty).

It’s not going to be easy – and I’d suggest taking it one field doing one conversion job at a time before trying to combine it all into a monolithic formula.

B

+16

Adnan_Ahmed
Inspiring
59 replies
5 years ago
September 8, 2019

Here’s what I did to achieve something similar, although it is for the Indian numeric system (uses lakhs instead of hundred thousand), so change it as you see fit.

I have a “Salary” field. First thing I did is create a formula field (called “Number”) which rounds off and then converts the “Salary” field to a text format using this formula: CONCATENATE(ROUND({Total salary},0),"")

Then, I used the below formula to convert it into words. Copy paste and open it in Notepad without any word wraps to make more sense of its structure. I’m sorry that I’m not going into too much details on each step, but I’ve already spent around an hour figuring this out and making it, so I could use a break now :stuck_out_tongue: . Still, hope this helps someone:

IF(AND(VALUE(RIGHT(Number,7))>=1000000, VALUE(RIGHT(Number,7))<1100000), "ten lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1100000, VALUE(RIGHT(Number,7))<1200000), "eleven lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1200000, VALUE(RIGHT(Number,7))<1300000), "twelve lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1300000, VALUE(RIGHT(Number,7))<1400000), "thirteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1400000, VALUE(RIGHT(Number,7))<1500000), "fourteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1500000, VALUE(RIGHT(Number,7))<1600000), "fifteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1600000, VALUE(RIGHT(Number,7))<1700000), "sixteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1700000, VALUE(RIGHT(Number,7))<1800000), "seventeen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1800000, VALUE(RIGHT(Number,7))<1900000), "eighteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1900000, VALUE(RIGHT(Number,7))<2000000), "nineteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=2000000, VALUE(RIGHT(Number,7))<3000000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,7))>=3000000, VALUE(RIGHT(Number,7))<4000000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,7))>=4000000, VALUE(RIGHT(Number,7))<5000000), "forty ")&
IF(AND(VALUE(RIGHT(Number,7))>=5000000, VALUE(RIGHT(Number,7))<6000000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,7))>=6000000, VALUE(RIGHT(Number,7))<7000000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,7))>=7000000, VALUE(RIGHT(Number,7))<8000000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,7))>=8000000, VALUE(RIGHT(Number,7))<9000000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,7))>=9000000, VALUE(RIGHT(Number,7))<10000000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=100000, VALUE(RIGHT(Number,6))<200000), "one lakh, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=200000, VALUE(RIGHT(Number,6))<300000), "two lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=300000, VALUE(RIGHT(Number,6))<400000), "three lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=400000, VALUE(RIGHT(Number,6))<500000), "four lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=500000, VALUE(RIGHT(Number,6))<600000), "five lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=600000, VALUE(RIGHT(Number,6))<700000), "six lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=700000, VALUE(RIGHT(Number,6))<800000), "seven lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=800000, VALUE(RIGHT(Number,6))<900000), "eight lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=900000, VALUE(RIGHT(Number,6))<1000000), "nine lakhs, ")
&
IF(AND(VALUE(RIGHT(Number,5))>=10000, VALUE(RIGHT(Number,5))<11000), "ten thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=11000, VALUE(RIGHT(Number,5))<12000), "eleven thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=12000, VALUE(RIGHT(Number,5))<13000), "twelve thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=13000, VALUE(RIGHT(Number,5))<14000), "thirteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=14000, VALUE(RIGHT(Number,5))<15000), "fourteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=15000, VALUE(RIGHT(Number,5))<16000), "fifteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=16000, VALUE(RIGHT(Number,5))<17000), "sixteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=17000, VALUE(RIGHT(Number,5))<18000), "seventeen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=18000, VALUE(RIGHT(Number,5))<19000), "eighteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=19000, VALUE(RIGHT(Number,5))<20000), "nineteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=20000, VALUE(RIGHT(Number,5))<30000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,5))>=30000, VALUE(RIGHT(Number,5))<40000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,5))>=40000, VALUE(RIGHT(Number,5))<50000), "forty ")&
IF(AND(VALUE(RIGHT(Number,5))>=50000, VALUE(RIGHT(Number,5))<60000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,5))>=60000, VALUE(RIGHT(Number,5))<70000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,5))>=70000, VALUE(RIGHT(Number,5))<80000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,5))>=80000, VALUE(RIGHT(Number,5))<90000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,5))>=90000, VALUE(RIGHT(Number,5))<100000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=1000, VALUE(RIGHT(Number,4))<2000), "one thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=2000, VALUE(RIGHT(Number,4))<3000), "two thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=3000, VALUE(RIGHT(Number,4))<4000), "three thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=4000, VALUE(RIGHT(Number,4))<5000), "four thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=5000, VALUE(RIGHT(Number,4))<6000), "five thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=6000, VALUE(RIGHT(Number,4))<7000), "six thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=7000, VALUE(RIGHT(Number,4))<8000), "seven thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=8000, VALUE(RIGHT(Number,4))<9000), "eight thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=9000, VALUE(RIGHT(Number,4))<10000), "nine thousand, ")
&
IF(AND(VALUE(RIGHT(Number,3))>=100, VALUE(RIGHT(Number,3))<200), "one hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=200, VALUE(RIGHT(Number,3))<300), "two hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=300, VALUE(RIGHT(Number,3))<400), "three hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=400, VALUE(RIGHT(Number,3))<500), "four hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=500, VALUE(RIGHT(Number,3))<600), "five hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=600, VALUE(RIGHT(Number,3))<700), "six hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=700, VALUE(RIGHT(Number,3))<800), "seven hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=800, VALUE(RIGHT(Number,3))<900), "eight hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=900, VALUE(RIGHT(Number,3))<1000), "nine hundred and ")
&
IF(VALUE(RIGHT(Number,2))=10, “ten”)&
IF(VALUE(RIGHT(Number,2))=11, “eleven”)&
IF(VALUE(RIGHT(Number,2))=12, “twelve”)&
IF(VALUE(RIGHT(Number,2))=13, “thirteen”)&
IF(VALUE(RIGHT(Number,2))=14, “fourteen”)&
IF(VALUE(RIGHT(Number,2))=15, “fifteen”)&
IF(VALUE(RIGHT(Number,2))=16, “sixteen”)&
IF(VALUE(RIGHT(Number,2))=17, “seventeen”)&
IF(VALUE(RIGHT(Number,2))=18, “eighteen”)&
IF(VALUE(RIGHT(Number,2))=19, “nineteen”)&
IF(AND(VALUE(RIGHT(Number,2))>=20, VALUE(RIGHT(Number,2))<30), "twenty ")&
IF(AND(VALUE(RIGHT(Number,2))>=30, VALUE(RIGHT(Number,2))<40), "thirty ")&
IF(AND(VALUE(RIGHT(Number,2))>=40, VALUE(RIGHT(Number,2))<50), "forty ")&
IF(AND(VALUE(RIGHT(Number,2))>=50, VALUE(RIGHT(Number,2))<60), "fifty ")&
IF(AND(VALUE(RIGHT(Number,2))>=60, VALUE(RIGHT(Number,2))<70), "sixty ")&
IF(AND(VALUE(RIGHT(Number,2))>=70, VALUE(RIGHT(Number,2))<80), "seventy ")&
IF(AND(VALUE(RIGHT(Number,2))>=80, VALUE(RIGHT(Number,2))<90), "eighty ")&
IF(AND(VALUE(RIGHT(Number,2))>=90, VALUE(RIGHT(Number,2))<100), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=1), “one”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=2), “two”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=3), “three”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=4), “four”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=5), “five”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=6), “six”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=7), “seven”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=8), “eight”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=9), “nine”)

L

B

+19

Bill_French
Inspiring
3264 replies
5 years ago
September 9, 2019

Adnan_Ahmed wrote:

Here’s what I did to achieve something similar, although it is for the Indian numeric system (uses lakhs instead of hundred thousand), so change it as you see fit.

I have a “Salary” field. First thing I did is create a formula field (called “Number”) which rounds off and then converts the “Salary” field to a text format using this formula: CONCATENATE(ROUND({Total salary},0),"")

Then, I used the below formula to convert it into words. Copy paste and open it in Notepad without any word wraps to make more sense of its structure. I’m sorry that I’m not going into too much details on each step, but I’ve already spent around an hour figuring this out and making it, so I could use a break now :stuck_out_tongue: . Still, hope this helps someone:

IF(AND(VALUE(RIGHT(Number,7))>=1000000, VALUE(RIGHT(Number,7))<1100000), "ten lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1100000, VALUE(RIGHT(Number,7))<1200000), "eleven lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1200000, VALUE(RIGHT(Number,7))<1300000), "twelve lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1300000, VALUE(RIGHT(Number,7))<1400000), "thirteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1400000, VALUE(RIGHT(Number,7))<1500000), "fourteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1500000, VALUE(RIGHT(Number,7))<1600000), "fifteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1600000, VALUE(RIGHT(Number,7))<1700000), "sixteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1700000, VALUE(RIGHT(Number,7))<1800000), "seventeen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1800000, VALUE(RIGHT(Number,7))<1900000), "eighteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1900000, VALUE(RIGHT(Number,7))<2000000), "nineteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=2000000, VALUE(RIGHT(Number,7))<3000000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,7))>=3000000, VALUE(RIGHT(Number,7))<4000000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,7))>=4000000, VALUE(RIGHT(Number,7))<5000000), "forty ")&
IF(AND(VALUE(RIGHT(Number,7))>=5000000, VALUE(RIGHT(Number,7))<6000000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,7))>=6000000, VALUE(RIGHT(Number,7))<7000000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,7))>=7000000, VALUE(RIGHT(Number,7))<8000000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,7))>=8000000, VALUE(RIGHT(Number,7))<9000000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,7))>=9000000, VALUE(RIGHT(Number,7))<10000000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=100000, VALUE(RIGHT(Number,6))<200000), "one lakh, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=200000, VALUE(RIGHT(Number,6))<300000), "two lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=300000, VALUE(RIGHT(Number,6))<400000), "three lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=400000, VALUE(RIGHT(Number,6))<500000), "four lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=500000, VALUE(RIGHT(Number,6))<600000), "five lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=600000, VALUE(RIGHT(Number,6))<700000), "six lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=700000, VALUE(RIGHT(Number,6))<800000), "seven lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=800000, VALUE(RIGHT(Number,6))<900000), "eight lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=900000, VALUE(RIGHT(Number,6))<1000000), "nine lakhs, ")
&
IF(AND(VALUE(RIGHT(Number,5))>=10000, VALUE(RIGHT(Number,5))<11000), "ten thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=11000, VALUE(RIGHT(Number,5))<12000), "eleven thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=12000, VALUE(RIGHT(Number,5))<13000), "twelve thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=13000, VALUE(RIGHT(Number,5))<14000), "thirteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=14000, VALUE(RIGHT(Number,5))<15000), "fourteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=15000, VALUE(RIGHT(Number,5))<16000), "fifteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=16000, VALUE(RIGHT(Number,5))<17000), "sixteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=17000, VALUE(RIGHT(Number,5))<18000), "seventeen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=18000, VALUE(RIGHT(Number,5))<19000), "eighteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=19000, VALUE(RIGHT(Number,5))<20000), "nineteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=20000, VALUE(RIGHT(Number,5))<30000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,5))>=30000, VALUE(RIGHT(Number,5))<40000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,5))>=40000, VALUE(RIGHT(Number,5))<50000), "forty ")&
IF(AND(VALUE(RIGHT(Number,5))>=50000, VALUE(RIGHT(Number,5))<60000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,5))>=60000, VALUE(RIGHT(Number,5))<70000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,5))>=70000, VALUE(RIGHT(Number,5))<80000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,5))>=80000, VALUE(RIGHT(Number,5))<90000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,5))>=90000, VALUE(RIGHT(Number,5))<100000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=1000, VALUE(RIGHT(Number,4))<2000), "one thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=2000, VALUE(RIGHT(Number,4))<3000), "two thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=3000, VALUE(RIGHT(Number,4))<4000), "three thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=4000, VALUE(RIGHT(Number,4))<5000), "four thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=5000, VALUE(RIGHT(Number,4))<6000), "five thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=6000, VALUE(RIGHT(Number,4))<7000), "six thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=7000, VALUE(RIGHT(Number,4))<8000), "seven thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=8000, VALUE(RIGHT(Number,4))<9000), "eight thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=9000, VALUE(RIGHT(Number,4))<10000), "nine thousand, ")
&
IF(AND(VALUE(RIGHT(Number,3))>=100, VALUE(RIGHT(Number,3))<200), "one hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=200, VALUE(RIGHT(Number,3))<300), "two hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=300, VALUE(RIGHT(Number,3))<400), "three hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=400, VALUE(RIGHT(Number,3))<500), "four hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=500, VALUE(RIGHT(Number,3))<600), "five hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=600, VALUE(RIGHT(Number,3))<700), "six hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=700, VALUE(RIGHT(Number,3))<800), "seven hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=800, VALUE(RIGHT(Number,3))<900), "eight hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=900, VALUE(RIGHT(Number,3))<1000), "nine hundred and ")
&
IF(VALUE(RIGHT(Number,2))=10, “ten”)&
IF(VALUE(RIGHT(Number,2))=11, “eleven”)&
IF(VALUE(RIGHT(Number,2))=12, “twelve”)&
IF(VALUE(RIGHT(Number,2))=13, “thirteen”)&
IF(VALUE(RIGHT(Number,2))=14, “fourteen”)&
IF(VALUE(RIGHT(Number,2))=15, “fifteen”)&
IF(VALUE(RIGHT(Number,2))=16, “sixteen”)&
IF(VALUE(RIGHT(Number,2))=17, “seventeen”)&
IF(VALUE(RIGHT(Number,2))=18, “eighteen”)&
IF(VALUE(RIGHT(Number,2))=19, “nineteen”)&
IF(AND(VALUE(RIGHT(Number,2))>=20, VALUE(RIGHT(Number,2))<30), "twenty ")&
IF(AND(VALUE(RIGHT(Number,2))>=30, VALUE(RIGHT(Number,2))<40), "thirty ")&
IF(AND(VALUE(RIGHT(Number,2))>=40, VALUE(RIGHT(Number,2))<50), "forty ")&
IF(AND(VALUE(RIGHT(Number,2))>=50, VALUE(RIGHT(Number,2))<60), "fifty ")&
IF(AND(VALUE(RIGHT(Number,2))>=60, VALUE(RIGHT(Number,2))<70), "sixty ")&
IF(AND(VALUE(RIGHT(Number,2))>=70, VALUE(RIGHT(Number,2))<80), "seventy ")&
IF(AND(VALUE(RIGHT(Number,2))>=80, VALUE(RIGHT(Number,2))<90), "eighty ")&
IF(AND(VALUE(RIGHT(Number,2))>=90, VALUE(RIGHT(Number,2))<100), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=1), “one”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=2), “two”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=3), “three”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=4), “four”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=5), “five”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=6), “six”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=7), “seven”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=8), “eight”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=9), “nine”)

One word - OMG!

That’s a serious amount of code to do one simple thing.

B

+19

Bill_French
Inspiring
3264 replies
5 years ago
September 9, 2019

This is a perfect example of why Airtable needs an extensible, integrated, and sanitized javascript engine. This javascript function does it all and supports thousands and lakhs. :winking_face:

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

function inWords (num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'only ' : '';
    return str;
}

M

J

Jeremy_Nash
New Participant
1 reply
4 years ago
April 13, 2020

Adnan_Ahmed wrote:

Here’s what I did to achieve something similar, although it is for the Indian numeric system (uses lakhs instead of hundred thousand), so change it as you see fit.

I have a “Salary” field. First thing I did is create a formula field (called “Number”) which rounds off and then converts the “Salary” field to a text format using this formula: CONCATENATE(ROUND({Total salary},0),"")

Then, I used the below formula to convert it into words. Copy paste and open it in Notepad without any word wraps to make more sense of its structure. I’m sorry that I’m not going into too much details on each step, but I’ve already spent around an hour figuring this out and making it, so I could use a break now :stuck_out_tongue: . Still, hope this helps someone:

IF(AND(VALUE(RIGHT(Number,7))>=1000000, VALUE(RIGHT(Number,7))<1100000), "ten lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1100000, VALUE(RIGHT(Number,7))<1200000), "eleven lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1200000, VALUE(RIGHT(Number,7))<1300000), "twelve lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1300000, VALUE(RIGHT(Number,7))<1400000), "thirteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1400000, VALUE(RIGHT(Number,7))<1500000), "fourteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1500000, VALUE(RIGHT(Number,7))<1600000), "fifteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1600000, VALUE(RIGHT(Number,7))<1700000), "sixteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1700000, VALUE(RIGHT(Number,7))<1800000), "seventeen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1800000, VALUE(RIGHT(Number,7))<1900000), "eighteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=1900000, VALUE(RIGHT(Number,7))<2000000), "nineteen lakhs, ")&
IF(AND(VALUE(RIGHT(Number,7))>=2000000, VALUE(RIGHT(Number,7))<3000000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,7))>=3000000, VALUE(RIGHT(Number,7))<4000000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,7))>=4000000, VALUE(RIGHT(Number,7))<5000000), "forty ")&
IF(AND(VALUE(RIGHT(Number,7))>=5000000, VALUE(RIGHT(Number,7))<6000000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,7))>=6000000, VALUE(RIGHT(Number,7))<7000000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,7))>=7000000, VALUE(RIGHT(Number,7))<8000000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,7))>=8000000, VALUE(RIGHT(Number,7))<9000000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,7))>=9000000, VALUE(RIGHT(Number,7))<10000000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=100000, VALUE(RIGHT(Number,6))<200000), "one lakh, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=200000, VALUE(RIGHT(Number,6))<300000), "two lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=300000, VALUE(RIGHT(Number,6))<400000), "three lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=400000, VALUE(RIGHT(Number,6))<500000), "four lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=500000, VALUE(RIGHT(Number,6))<600000), "five lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=600000, VALUE(RIGHT(Number,6))<700000), "six lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=700000, VALUE(RIGHT(Number,6))<800000), "seven lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=800000, VALUE(RIGHT(Number,6))<900000), "eight lakhs, ")&
IF(AND(OR(VALUE(RIGHT(Number,7))<1000000, (VALUE(RIGHT(Number,7))>2000000)), VALUE(RIGHT(Number,6))>=900000, VALUE(RIGHT(Number,6))<1000000), "nine lakhs, ")
&
IF(AND(VALUE(RIGHT(Number,5))>=10000, VALUE(RIGHT(Number,5))<11000), "ten thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=11000, VALUE(RIGHT(Number,5))<12000), "eleven thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=12000, VALUE(RIGHT(Number,5))<13000), "twelve thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=13000, VALUE(RIGHT(Number,5))<14000), "thirteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=14000, VALUE(RIGHT(Number,5))<15000), "fourteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=15000, VALUE(RIGHT(Number,5))<16000), "fifteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=16000, VALUE(RIGHT(Number,5))<17000), "sixteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=17000, VALUE(RIGHT(Number,5))<18000), "seventeen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=18000, VALUE(RIGHT(Number,5))<19000), "eighteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=19000, VALUE(RIGHT(Number,5))<20000), "nineteen thousand, ")&
IF(AND(VALUE(RIGHT(Number,5))>=20000, VALUE(RIGHT(Number,5))<30000), "twenty ")&
IF(AND(VALUE(RIGHT(Number,5))>=30000, VALUE(RIGHT(Number,5))<40000), "thirty ")&
IF(AND(VALUE(RIGHT(Number,5))>=40000, VALUE(RIGHT(Number,5))<50000), "forty ")&
IF(AND(VALUE(RIGHT(Number,5))>=50000, VALUE(RIGHT(Number,5))<60000), "fifty ")&
IF(AND(VALUE(RIGHT(Number,5))>=60000, VALUE(RIGHT(Number,5))<70000), "sixty ")&
IF(AND(VALUE(RIGHT(Number,5))>=70000, VALUE(RIGHT(Number,5))<80000), "seventy ")&
IF(AND(VALUE(RIGHT(Number,5))>=80000, VALUE(RIGHT(Number,5))<90000), "eighty ")&
IF(AND(VALUE(RIGHT(Number,5))>=90000, VALUE(RIGHT(Number,5))<100000), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=1000, VALUE(RIGHT(Number,4))<2000), "one thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=2000, VALUE(RIGHT(Number,4))<3000), "two thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=3000, VALUE(RIGHT(Number,4))<4000), "three thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=4000, VALUE(RIGHT(Number,4))<5000), "four thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=5000, VALUE(RIGHT(Number,4))<6000), "five thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=6000, VALUE(RIGHT(Number,4))<7000), "six thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=7000, VALUE(RIGHT(Number,4))<8000), "seven thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=8000, VALUE(RIGHT(Number,4))<9000), "eight thousand, ")&
IF(AND(OR(VALUE(RIGHT(Number,5))<10000, (VALUE(RIGHT(Number,5))>20000)), VALUE(RIGHT(Number,4))>=9000, VALUE(RIGHT(Number,4))<10000), "nine thousand, ")
&
IF(AND(VALUE(RIGHT(Number,3))>=100, VALUE(RIGHT(Number,3))<200), "one hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=200, VALUE(RIGHT(Number,3))<300), "two hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=300, VALUE(RIGHT(Number,3))<400), "three hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=400, VALUE(RIGHT(Number,3))<500), "four hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=500, VALUE(RIGHT(Number,3))<600), "five hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=600, VALUE(RIGHT(Number,3))<700), "six hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=700, VALUE(RIGHT(Number,3))<800), "seven hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=800, VALUE(RIGHT(Number,3))<900), "eight hundred and ")&
IF(AND(VALUE(RIGHT(Number,3))>=900, VALUE(RIGHT(Number,3))<1000), "nine hundred and ")
&
IF(VALUE(RIGHT(Number,2))=10, “ten”)&
IF(VALUE(RIGHT(Number,2))=11, “eleven”)&
IF(VALUE(RIGHT(Number,2))=12, “twelve”)&
IF(VALUE(RIGHT(Number,2))=13, “thirteen”)&
IF(VALUE(RIGHT(Number,2))=14, “fourteen”)&
IF(VALUE(RIGHT(Number,2))=15, “fifteen”)&
IF(VALUE(RIGHT(Number,2))=16, “sixteen”)&
IF(VALUE(RIGHT(Number,2))=17, “seventeen”)&
IF(VALUE(RIGHT(Number,2))=18, “eighteen”)&
IF(VALUE(RIGHT(Number,2))=19, “nineteen”)&
IF(AND(VALUE(RIGHT(Number,2))>=20, VALUE(RIGHT(Number,2))<30), "twenty ")&
IF(AND(VALUE(RIGHT(Number,2))>=30, VALUE(RIGHT(Number,2))<40), "thirty ")&
IF(AND(VALUE(RIGHT(Number,2))>=40, VALUE(RIGHT(Number,2))<50), "forty ")&
IF(AND(VALUE(RIGHT(Number,2))>=50, VALUE(RIGHT(Number,2))<60), "fifty ")&
IF(AND(VALUE(RIGHT(Number,2))>=60, VALUE(RIGHT(Number,2))<70), "sixty ")&
IF(AND(VALUE(RIGHT(Number,2))>=70, VALUE(RIGHT(Number,2))<80), "seventy ")&
IF(AND(VALUE(RIGHT(Number,2))>=80, VALUE(RIGHT(Number,2))<90), "eighty ")&
IF(AND(VALUE(RIGHT(Number,2))>=90, VALUE(RIGHT(Number,2))<100), "ninety ")
&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=1), “one”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=2), “two”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=3), “three”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=4), “four”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=5), “five”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=6), “six”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=7), “seven”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=8), “eight”)&
IF(AND(OR(VALUE(RIGHT(Number,2))<10, (VALUE(RIGHT(Number,2))>20)), VALUE(RIGHT(Number,1))=9), “nine”)

When I try to use this formula, I get an error. Has it worked for anyone else?

A

L

Liz_Treacy
New Participant
4 replies
4 years ago
June 7, 2020

Jeremy_Nash wrote:

When I try to use this formula, I get an error. Has it worked for anyone else?

Yes I got it to work for me, but there are a couple things:

One problem is the quotation marks: Airtable needs quotation marks that are " rather than ” For some reason, the quotations toward the end of the code they pasted are incorrect - specifically the ones around the numbers “two” through “nineteen” - If you update all those incorrect quotations, it should work. (For some reason, if you paste it in to this text editor, they don’t all show up correctly)
Additionally, based upon Jarvis_Digital’s code, the number that you want to be translated into the spelled out version needs to be in text format. The VALUE() formula expects text. To make this work, since the number field I want to be translated to text (mine is titled NumberValue) is a number value and not text, I created a new field called Number which uses the following formula CONCATENATE(NumberValue). This turns it into a text format. So now, I can use Jarvis_Digital’s code because my Number field is read as text.

Now, Jarvis_Digital’s updated code (with correct quotation marks and with the Number field being text) works in Airtable as a valid formula, BUT it still doesn’t completely work as desired. For example, 30000 returns “thirty” rather than “thirty thousand” (20,000, 30,000, 40,000 through 90,000 all have this issue, although 22,000 and 95,000, etc work as expected). Also 200 returns “two hundred and” rather than "two hundred’… I am going to work on this formula to fix it to work for general standard English numbers, though I will likely start from scratch and can share once I’m done.

B

+19

Bill_French
Inspiring
3264 replies
4 years ago
June 7, 2020

Liz_Treacy wrote:

Yes I got it to work for me, but there are a couple things:

One problem is the quotation marks: Airtable needs quotation marks that are " rather than ” For some reason, the quotations toward the end of the code they pasted are incorrect - specifically the ones around the numbers “two” through “nineteen” - If you update all those incorrect quotations, it should work. (For some reason, if you paste it in to this text editor, they don’t all show up correctly)
Additionally, based upon Jarvis_Digital’s code, the number that you want to be translated into the spelled out version needs to be in text format. The VALUE() formula expects text. To make this work, since the number field I want to be translated to text (mine is titled NumberValue) is a number value and not text, I created a new field called Number which uses the following formula CONCATENATE(NumberValue). This turns it into a text format. So now, I can use Jarvis_Digital’s code because my Number field is read as text.

Now, Jarvis_Digital’s updated code (with correct quotation marks and with the Number field being text) works in Airtable as a valid formula, BUT it still doesn’t completely work as desired. For example, 30000 returns “thirty” rather than “thirty thousand” (20,000, 30,000, 40,000 through 90,000 all have this issue, although 22,000 and 95,000, etc work as expected). Also 200 returns “two hundred and” rather than "two hundred’… I am going to work on this formula to fix it to work for general standard English numbers, though I will likely start from scratch and can share once I’m done.

Hi @Liz_Treacy and welcome to the Airtable community!

That’s an encoding issue introduced by web browsers during a copy/paste process. They probably pasted working code and wither their browser or the community platform mangled the UTF-8 encoding prescribed.

The javascript code example provided (which is one-fifteenth the size of the formula and one-thirtieth as complex) was created long before Script Blocks were introduced. For giggles, I dropped it into a Script block and it worked without modification. Top-of-the-hat to the script block engineers, eh?

Certainly, it’s nice to be able to transform the data values inline as a tidy formula field, but if you’re weary from creating new fields to fix other fields and compounding the complexities of your apps with added formula fields, a script block is an ideal approach to simplify both interpretations of data and transformations outcomes that can be stored in actual fields which have no constraints that formula fields are subject to in other features of Airtable.

Indeed, script blocks are not automated [yet]. :slightly_smiling_face: But, you can run them with a button and I predict they will be automated in the very near term. I also predict that Airtabl e engineers will find the idea of Script Blocks irresistible as an extensible platform for creating new custom formulas. Before Covid-19 makes another swing at us in the fall, we will likely see script blocks elevated as a key infrastructure to overcoming the cul-de-sac of formulas that are not ideally skilled to address complex transformation problems like this.

One final remark - there are non-trivial performance costs associated with large and complex formula logic. Imagine that once you create the perfect number-to-words formula and drop it into a new column – when you render that table in a browser, every line of the formula must recalculate for every record in the table and additionally with every scroll, every filtered refresh, every load, every new record, and for the rest of the apps’ lifetime. Any tables that are large or likely to grow larger will only make the refresh time increasingly more sluggish for users. Something has to perform all those tests and that something is your CPU running your browser.

output.markdown("# Number to Words Converion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 22000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 95000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

L

Liz_Treacy
New Participant
4 replies
4 years ago
June 7, 2020

Bill_French wrote:

Hi @Liz_Treacy and welcome to the Airtable community!

That’s an encoding issue introduced by web browsers during a copy/paste process. They probably pasted working code and wither their browser or the community platform mangled the UTF-8 encoding prescribed.

The javascript code example provided (which is one-fifteenth the size of the formula and one-thirtieth as complex) was created long before Script Blocks were introduced. For giggles, I dropped it into a Script block and it worked without modification. Top-of-the-hat to the script block engineers, eh?

Certainly, it’s nice to be able to transform the data values inline as a tidy formula field, but if you’re weary from creating new fields to fix other fields and compounding the complexities of your apps with added formula fields, a script block is an ideal approach to simplify both interpretations of data and transformations outcomes that can be stored in actual fields which have no constraints that formula fields are subject to in other features of Airtable.

Indeed, script blocks are not automated [yet]. :slightly_smiling_face: But, you can run them with a button and I predict they will be automated in the very near term. I also predict that Airtabl e engineers will find the idea of Script Blocks irresistible as an extensible platform for creating new custom formulas. Before Covid-19 makes another swing at us in the fall, we will likely see script blocks elevated as a key infrastructure to overcoming the cul-de-sac of formulas that are not ideally skilled to address complex transformation problems like this.

One final remark - there are non-trivial performance costs associated with large and complex formula logic. Imagine that once you create the perfect number-to-words formula and drop it into a new column – when you render that table in a browser, every line of the formula must recalculate for every record in the table and additionally with every scroll, every filtered refresh, every load, every new record, and for the rest of the apps’ lifetime. Any tables that are large or likely to grow larger will only make the refresh time increasingly more sluggish for users. Something has to perform all those tests and that something is your CPU running your browser.

output.markdown("# Number to Words Converion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 22000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 95000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

Thanks, Bill! I didn’t know scripting blocks existed, so thanks for the introduction! Also - I found a bug in the code you provided. The number 900000 was returning nine hundred, so I got a bit of help to fix that bug, and pasted the update below.

Now - I just need to learn how to get this script to run on one field in my table, and update another field on my table using the inWords function! I still haven’t gotten the hang of how to load all the records in my table.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];


var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 22000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 95000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

L

Liz_Treacy
New Participant
4 replies
4 years ago
June 7, 2020

Liz_Treacy wrote:

Thanks, Bill! I didn’t know scripting blocks existed, so thanks for the introduction! Also - I found a bug in the code you provided. The number 900000 was returning nine hundred, so I got a bit of help to fix that bug, and pasted the update below.

Now - I just need to learn how to get this script to run on one field in my table, and update another field on my table using the inWords function! I still haven’t gotten the hang of how to load all the records in my table.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];


var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 22000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 95000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

Just figured it out!! For those who want to use this to update field SpelledNumber from field Number, this is the total of the code I used in the Scripting Block:

Note: 9,000,000 returns ninety hundred thousand and 90,000,000 returns nine crore, both of which are technically correct but not quite what you might want for your own purposes, so the code would take a bit more massaging to have it use millions in the way you want.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// These are just examples to show that the function below is working as expected.
var thisValue = 7;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 12;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 198;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 52000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

// Change this to the name of a table in your base
let table = base.getTable('Table 1');
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'SpelledNumber': inWords(record.getCellValue('Number')),
    });
}

B

+19

Bill_French
Inspiring
3264 replies
4 years ago
June 7, 2020

Liz_Treacy wrote:

Thanks, Bill! I didn’t know scripting blocks existed, so thanks for the introduction! Also - I found a bug in the code you provided. The number 900000 was returning nine hundred, so I got a bit of help to fix that bug, and pasted the update below.

Now - I just need to learn how to get this script to run on one field in my table, and update another field on my table using the inWords function! I still haven’t gotten the hang of how to load all the records in my table.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];


var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 22000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 95000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

You bet. This is one of those perfect ways to explore new approaches to manicure your data in Airtable.

M

Marian_Kirstie_
New Participant
1 reply
4 years ago
September 3, 2020

Bill_French wrote:

This is a perfect example of why Airtable needs an extensible, integrated, and sanitized javascript engine. This javascript function does it all and supports thousands and lakhs. :winking_face:

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

function inWords (num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'only ' : '';
    return str;
}

Thank you so much, Bill! Great help! :grinning_face_with_big_eyes:

Modified this to include million, decimal as cents and currency (peso).

output.markdown("Number to Words CV");
var a = [’’,'One ','Two ','Three ','Four ', 'Five ','Six ','Seven ','Eight ','Nine ','Ten ','Eleven ','Twelve ','Thirteen ','Fourteen ','Fifteen ','Sixteen ','Seventeen ','Eighteen ','Nineteen '];

var b = [’’, ‘’, ‘Twenty’,‘Thirty’,‘Forty’,‘Fifty’, ‘Sixty’,‘Seventy’,‘Eighty’,‘Ninety’];

// These are just examples to show that the function below is working as expected.

var thisValue = parseInt(“5”+“0”);

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 12;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 198;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 3124;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 30000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 52000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 323000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 20;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 9000000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

function inWords(num) {
if ((num = num.toString()).length > 9) return ‘overflow’;
n = (‘000000000’ + num).substr(-9).match(/^(\d{1})(\d{2})(\d{1})(\d{2})(\d{1})(\d{2})$/);
if (!n)
return;
var str = ‘’;
var hundredM=false;
if(n[1] != 0){
str += (a[Number(n[1])] || b[n[1][0]] + ’ ’ + a[n[1][1]]) + 'Hundred ';
hundredM = true;
}
else{
str += ‘’;
}
str += (n[2] != 0||hundredM) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + ' Million ' : '';

var hundredThousand = false;
if (n[3] != 0) {
    str += (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'Hundred ';
    hundredThousand = true;
}
str += (n[4] != 0 || hundredThousand) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'Thousand ' : '';
str += (n[5] != 0) ? (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'Hundred ' : '';
str += (n[6] != 0) ? ((str != '') ? ' ' : '') + (a[Number(n[6])] || b[n[6][0]] + ' ' + a[n[6][1]]) + '' : '';
return str;
}

function splitspell(numb){
var strnumb= “”;
var strcomb="";
var cent="";
var ind;
strnumb=numb.toString();
ind = strnumb.indexOf(".");
if(ind == -1){
return inWords(numb).concat(" Pesos Only");
}
if((strnumb.length-ind)==2){
    cent = "Pesos and " + inWords(parseInt(strnumb.substring(ind+1)+"0"))+ " Cents Only"; 
    strcomb = inWords(parseInt(strnumb.substring(0,ind)));
    return strcomb.concat(cent);
    //output.markdown(strcomb.concat(cent));
}
if((strnumb.length-ind)==3){
    cent = "Pesos and " + inWords(parseInt(strnumb.substring(ind+1)))+ " Cents Only"; 
    strcomb = inWords(parseInt(strnumb.substring(0,ind)));
    return strcomb.concat(cent);
}
}

// Change this to the name of a table in your base
let table = base.getTable(‘Sample’);
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
await table.updateRecordAsync(record, {
// Change these names to fields in your base
‘Spelled Amount’: splitspell(record.getCellValue(‘Amount’)),
});
}

B

T

H

Hady_Abdelnour
Known Participant
11 replies
4 years ago
October 12, 2020

Bill_French wrote:

This is a perfect example of why Airtable needs an extensible, integrated, and sanitized javascript engine. This javascript function does it all and supports thousands and lakhs. :winking_face:

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

function inWords (num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'only ' : '';
    return str;
}

Bill.French:

var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

function inWords (num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'only ' : '';
    return str;

Question Bill, where do I put in this script, the column that feeds the numbers and where do I put the column that takes the texts?
Sorry but I am not a developer, trying my best

B

+19

Bill_French
Inspiring
3264 replies
4 years ago
October 12, 2020

Hady_Abdelnour wrote:

Bill.French:

var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

function inWords (num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'only ' : '';
    return str;

Question Bill, where do I put in this script, the column that feeds the numbers and where do I put the column that takes the texts?
Sorry but I am not a developer, trying my best

Okay - imagine a table like this - with a column of numbers (Numbers) and a column we want to populate with the words (Words).

The script to look at each Numbers cell and fill in the Words cell is below. I needs to be copied into a script block inside a base where a table named Math Functions Demo with at least the two columns (Numbers and Words) exists.

This is simple and rudimentary but enough to demonstrate a likely pathway to address your question. In practice, something like this should run automatically when the Numbers column is modified. This can be achieved by converting this sample code into a Script Action.

output.markdown('# Numbers to Words');

// establish the words arrays
let a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
let b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// get the name of the table
let table = base.getTable("Math Functions Demo");

// get the records from this table
let result2 = await table.selectRecordsAsync();

// Update the records
for (let record of result2.records) {
    output.text(record.getCellValue("Numbers") + " = " + inWords(a, b, record.getCellValue("Numbers")));
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        "Words": inWords(a, b, record.getCellValue("Numbers")),
    });
}

function inWords(a, b, num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         var n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

J

S

H

Hady_Abdelnour
Known Participant
11 replies
4 years ago
October 13, 2020

Thank you so much Bill, it worked perfectly, really appreciate it

B

T

+8

Traci_Franssen
Participating Frequently
11 replies
4 years ago
January 21, 2021

Marian_Kirstie_ wrote:

Thank you so much, Bill! Great help! :grinning_face_with_big_eyes:

Modified this to include million, decimal as cents and currency (peso).

output.markdown("Number to Words CV");
var a = [’’,'One ','Two ','Three ','Four ', 'Five ','Six ','Seven ','Eight ','Nine ','Ten ','Eleven ','Twelve ','Thirteen ','Fourteen ','Fifteen ','Sixteen ','Seventeen ','Eighteen ','Nineteen '];

var b = [’’, ‘’, ‘Twenty’,‘Thirty’,‘Forty’,‘Fifty’, ‘Sixty’,‘Seventy’,‘Eighty’,‘Ninety’];

// These are just examples to show that the function below is working as expected.

var thisValue = parseInt(“5”+“0”);

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 12;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 198;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 3124;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 30000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 52000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 323000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 20;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

var thisValue = 9000000;

output.markdown("" + thisValue.toString() + " expressed as text: " + inWords(thisValue) + "");

function inWords(num) {
if ((num = num.toString()).length > 9) return ‘overflow’;
n = (‘000000000’ + num).substr(-9).match(/^(\d{1})(\d{2})(\d{1})(\d{2})(\d{1})(\d{2})$/);
if (!n)
return;
var str = ‘’;
var hundredM=false;
if(n[1] != 0){
str += (a[Number(n[1])] || b[n[1][0]] + ’ ’ + a[n[1][1]]) + 'Hundred ';
hundredM = true;
}
else{
str += ‘’;
}
str += (n[2] != 0||hundredM) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + ' Million ' : '';

var hundredThousand = false;
if (n[3] != 0) {
    str += (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'Hundred ';
    hundredThousand = true;
}
str += (n[4] != 0 || hundredThousand) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'Thousand ' : '';
str += (n[5] != 0) ? (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + 'Hundred ' : '';
str += (n[6] != 0) ? ((str != '') ? ' ' : '') + (a[Number(n[6])] || b[n[6][0]] + ' ' + a[n[6][1]]) + '' : '';
return str;
}

function splitspell(numb){
var strnumb= “”;
var strcomb="";
var cent="";
var ind;
strnumb=numb.toString();
ind = strnumb.indexOf(".");
if(ind == -1){
return inWords(numb).concat(" Pesos Only");
}
if((strnumb.length-ind)==2){
    cent = "Pesos and " + inWords(parseInt(strnumb.substring(ind+1)+"0"))+ " Cents Only"; 
    strcomb = inWords(parseInt(strnumb.substring(0,ind)));
    return strcomb.concat(cent);
    //output.markdown(strcomb.concat(cent));
}
if((strnumb.length-ind)==3){
    cent = "Pesos and " + inWords(parseInt(strnumb.substring(ind+1)))+ " Cents Only"; 
    strcomb = inWords(parseInt(strnumb.substring(0,ind)));
    return strcomb.concat(cent);
}
}

// Change this to the name of a table in your base
let table = base.getTable(‘Sample’);
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
await table.updateRecordAsync(record, {
// Change these names to fields in your base
‘Spelled Amount’: splitspell(record.getCellValue(‘Amount’)),
});
}

HUGE Thank you to everyone in this thread!

I have taken the above and modified to print checks with dollars and fractions of dollars,

output.markdown(“Number to Words CV”);

var a = [’ ','One ','Two ','Three ','Four ', 'Five ','Six ','Seven ','Eight ','Nine ','Ten ','Eleven ','Twelve ','Thirteen ','Fourteen ','Fifteen ','Sixteen ','Seventeen ','Eighteen ','Nineteen '];

var b = [’ ', ’ ', ‘Twenty’,‘Thirty’,‘Forty’,‘Fifty’, ‘Sixty’,‘Seventy’,‘Eighty’,‘Ninety’];

function inWords(num) {
if ((num = num.toString()).length > 9) return ‘overflow’;
n = (‘000000000’ + num).substr(-9).match(/^(\d{1})(\d{2})(\d{1})(\d{2})(\d{1})(\d{2})$/);
if (!n)
return;
var str = ‘’;
var hundredM=false;
if(n[1] != 0){
str += (a[Number(n[1])] || b[n[1][0]] + ’ ’ + a[n[1][1]]) + 'Hundred ';
hundredM = true;
}
else{
str += ’ ';
}

str += (n[2] != 0||hundredM) ? (a[Number(n[2])] || b[n[2][0]] + ’ ’ + a[n[2][1]]) + ’ Million ’ : ‘’;

var hundredThousand = false;
if (n[3] != 0) {
str += (a[Number(n[3])] || b[n[3][0]] + ’ ’ + a[n[3][1]]) + 'Hundred ';
hundredThousand = true;
}
str += (n[4] != 0 || hundredThousand) ? (a[Number(n[4])] || b[n[4][0]] + ’ ’ + a[n[4][1]]) + 'Thousand ’ : ‘’;
str += (n[5] != 0) ? (a[Number(n[5])] || b[n[5][0]] + ’ ’ + a[n[5][1]]) + 'Hundred ’ : ‘’;
str += (n[6] != 0) ? ((str != ‘’) ? ‘’ : ‘’) + (a[Number(n[6])] || b[n[6][0]] + ’ ’ + a[n[6][1]]) + ‘’ : ‘’;
return str;
}

function splitspell(numb){
var strnumb= ’ ';
var strcomb="";
var cent="";
var ind;
strnumb=numb.toString();
ind = strnumb.indexOf(".");
if(ind == -1){
return inWords(numb).concat("and 00/100 ");
}

if((strnumb.length-ind)==2){
cent = "and " + (strnumb.substring(ind+1)+“0”) + “/100”;
strcomb = inWords(parseInt(strnumb.substring(0,ind)));
return strcomb.concat(cent);
//output.markdown(strcomb.concat(cent));
}
if((strnumb.length-ind)==3){
cent = "and " + strnumb.substring(ind+1)+ "/100 ";
strcomb = inWords(parseInt(strnumb.substring(0,ind)));
return strcomb.concat(cent);
}
}

// Change this to the name of a table in your base
let table = base.getTable(‘PaymentChecks’);
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
await table.updateRecordAsync(record, {
// Change these names to fields in your base
‘CkAmtInWords’: splitspell(record.getCellValue(‘Check Amount’)),
});
}

I

+4

Issam_Chaouch
Inspiring
10 replies
4 years ago
March 1, 2021

Bill_French wrote:

Okay - imagine a table like this - with a column of numbers (Numbers) and a column we want to populate with the words (Words).

The script to look at each Numbers cell and fill in the Words cell is below. I needs to be copied into a script block inside a base where a table named Math Functions Demo with at least the two columns (Numbers and Words) exists.

This is simple and rudimentary but enough to demonstrate a likely pathway to address your question. In practice, something like this should run automatically when the Numbers column is modified. This can be achieved by converting this sample code into a Script Action.

output.markdown('# Numbers to Words');

// establish the words arrays
let a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
let b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// get the name of the table
let table = base.getTable("Math Functions Demo");

// get the records from this table
let result2 = await table.selectRecordsAsync();

// Update the records
for (let record of result2.records) {
    output.text(record.getCellValue("Numbers") + " = " + inWords(a, b, record.getCellValue("Numbers")));
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        "Words": inWords(a, b, record.getCellValue("Numbers")),
    });
}

function inWords(a, b, num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         var n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

Hi Bill,
This is Issam from France needing your help!
I am not a developer at all, but I am used to functions (Excel & Airtable).
I need to write in words the Salary of people in my table called “Animateurs”.
And as you’ve wrote earlier in this conversation, this is too complex to use a formula!
Then I found this script that seems working.
I tried to run it in my base, but I get a Type error message (see screenshot attached)
The salary field is called “Salary”, and the destination field is called “Words Salary”.
Can you please help me to find my error.
Thanks

A

+4

Aaron_Buchinger
Participating Frequently
5 replies
3 years ago
February 22, 2022

Liz_Treacy wrote:

Just figured it out!! For those who want to use this to update field SpelledNumber from field Number, this is the total of the code I used in the Scripting Block:

Note: 9,000,000 returns ninety hundred thousand and 90,000,000 returns nine crore, both of which are technically correct but not quite what you might want for your own purposes, so the code would take a bit more massaging to have it use millions in the way you want.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// These are just examples to show that the function below is working as expected.
var thisValue = 7;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 12;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 198;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 52000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

// Change this to the name of a table in your base
let table = base.getTable('Table 1');
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'SpelledNumber': inWords(record.getCellValue('Number')),
    });
}

I did your code however I’m receiving this Error.

Since I’m not a programmer by trade I don’t know how to read this. I made sure to name everything in may base/ table as your example

“TypeError: Cannot read properties of null (reading ‘toString’)
at inWords on line 36
at main on line 60”

L

Liz_Treacy
New Participant
4 replies
3 years ago
February 22, 2022

Aaron_Buchinger wrote:

I did your code however I’m receiving this Error.

Since I’m not a programmer by trade I don’t know how to read this. I made sure to name everything in may base/ table as your example

“TypeError: Cannot read properties of null (reading ‘toString’)
at inWords on line 36
at main on line 60”

Hi Aaron - I think there are a couple things you could change:

1 - In my script, I have added an extra line as part of the function inWords – the if (!num) return “”****; – part, shown below, to account for any fields that might not include an actual number.

function inWords(num) {

if (!num) return “”; // ← insert this line

2 - You should also check to make sure that the field you are feeding the function to be converted is a number field type, and not some other type of field that could include a non-number.

Hope that helps!

S

+5

Sean_Christense
Known Participant
10 replies
2 years ago
May 6, 2022

Liz_Treacy wrote:

Hi Aaron - I think there are a couple things you could change:

1 - In my script, I have added an extra line as part of the function inWords – the if (!num) return “”****; – part, shown below, to account for any fields that might not include an actual number.

function inWords(num) {

if (!num) return “”; // ← insert this line

2 - You should also check to make sure that the field you are feeding the function to be converted is a number field type, and not some other type of field that could include a non-number.

Hope that helps!

Hi!

This script is wonderful, and thanks Liz for the fix, that fixed it for me!

I’m trying to add this script to an automation, where it only updates the value for a specific record pulled from the automation, rather than every record in the table. I’ve tried editing the script but I ended up breaking too many things.

Could anyone help with this?

Thanks!
Sean

M

Mattie_Johnson
New Participant
1 reply
2 years ago
June 23, 2022

Thank you so much , very helpful!

S

+2

Sebastien_Munie
New Participant
2 replies
2 years ago
February 4, 2023

Hi,

I think I found a laziest way to proceed. It seems we can use the Google sheet extension called NumberText. Then with an automation, we can request Google Sheet.

T

+2

Tyler_Surrency
New Participant
3 replies
1 year ago
November 9, 2023

Liz_Treacy wrote:

Just figured it out!! For those who want to use this to update field SpelledNumber from field Number, this is the total of the code I used in the Scripting Block:

Note: 9,000,000 returns ninety hundred thousand and 90,000,000 returns nine crore, both of which are technically correct but not quite what you might want for your own purposes, so the code would take a bit more massaging to have it use millions in the way you want.

output.markdown("# Number to Words Conversion");

var a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
var b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// These are just examples to show that the function below is working as expected.
var thisValue = 7;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 12;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 198;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 3124;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 30000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 52000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 323000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 475232;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");

var thisValue = 900000;
output.markdown("**" + thisValue.toString() + "** expressed as text: **" + inWords(thisValue) + "**");


function inWords(num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

// Change this to the name of a table in your base
let table = base.getTable('Table 1');
let result2 = await table.selectRecordsAsync();
// Update the records
for (let record of result2.records) {
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'SpelledNumber': inWords(record.getCellValue('Number')),
    });
}

Is there a way to have this only run in a certain view? I have lots of records and do not want all the records to run every time I run this script.

S

+2

Sebastien_Munie
New Participant
2 replies
1 year ago
November 10, 2023

Tyler_Surrency wrote:

Is there a way to have this only run in a certain view? I have lots of records and do not want all the records to run every time I run this script.

I think you can, for exemple through Make, specify a view instead of a table.

K

+2

KPFS
New Participant
4 replies
1 year ago
March 28, 2024

Bill_French wrote:

Okay - imagine a table like this - with a column of numbers (Numbers) and a column we want to populate with the words (Words).

The script to look at each Numbers cell and fill in the Words cell is below. I needs to be copied into a script block inside a base where a table named Math Functions Demo with at least the two columns (Numbers and Words) exists.

This is simple and rudimentary but enough to demonstrate a likely pathway to address your question. In practice, something like this should run automatically when the Numbers column is modified. This can be achieved by converting this sample code into a Script Action.

output.markdown('# Numbers to Words');

// establish the words arrays
let a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
let b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// get the name of the table
let table = base.getTable("Math Functions Demo");

// get the records from this table
let result2 = await table.selectRecordsAsync();

// Update the records
for (let record of result2.records) {
    output.text(record.getCellValue("Numbers") + " = " + inWords(a, b, record.getCellValue("Numbers")));
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        "Words": inWords(a, b, record.getCellValue("Numbers")),
    });
}

function inWords(a, b, num) {
    if ((num = num.toString()).length > 9) return 'overflow';
         var n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n)
        return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    var hundredThousand = false;
    if (n[2] != 0) {
        str += (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'hundred ';
        hundredThousand = true
    }
    str += (n[3] != 0 || hundredThousand) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

I transform the script for Indian number format like Lakhs and Crores with the reference from your script code. Thank you.

// establish the words arrays
let a = ['','one ','two ','three ','four ', 'five ','six ','seven ','eight ','nine ','ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
let b = ['', '', 'twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety'];

// get the name of the table
let table = base.getTable("Testing");

// get the records from this table
let result2 = await table.selectRecordsAsync();

// Update the records
for (let record of result2.records) {
    output.text(record.getCellValue("Numbers") + " = " + inWords(a, b, record.getCellValue("Numbers")));
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        "Words": inWords(a, b, record.getCellValue("Numbers")),
    });
}

function inWords(a, b, num) {
    if ((num = num.toString()).length > 9) return 'overflow';
    var n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n) return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + 'crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + 'lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + 'thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + 'hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + '' : '';
    return str;
}

My Table name is "Testing" and the Number stored column name is "Numbers" and the "Words" is column where the words will store.

See the Screenshot the script works.

24 replies

1 Attachments

Reply

Most helpful members this week

1 Attachments

Reply

Related topics

Help Needed: Airtable Script to Create and Maintain a Junction Table Not Working

Airtable Script Help

Automation script exceeds 30 sec limiticon

Send specific fields to Zapier on Airtable Button Clickicon

Script for search results run on all records - error helpicon

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded