Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 05, 2018 12:13 PM
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?
Dec 05, 2018 12:34 PM
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.
Sep 08, 2019 08:29 AM
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”)
Sep 08, 2019 06:51 PM
One word - OMG!
That’s a serious amount of code to do one simple thing.
Sep 08, 2019 06:56 PM
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;
}
Apr 13, 2020 07:55 AM
When I try to use this formula, I get an error. Has it worked for anyone else?
Jun 07, 2020 10:54 AM
Yes I got it to work for me, but there are a couple things:
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.
Jun 07, 2020 11:52 AM
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;
}
Jun 07, 2020 12:57 PM
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;
}
Jun 07, 2020 01:25 PM
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')),
});
}