Re: Formula for Number to Text

jowan_qupty · ‎Dec 05, 2018

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?

Jeremy_Oglesby · ‎Dec 05, 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.

Adnan_Ahmed · ‎Sep 08, 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”)

Bill_French · ‎Sep 08, 2019

One word - OMG!

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

Bill_French · ‎Sep 08, 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;
}

Jeremy_Nash · ‎Apr 13, 2020

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

Liz_Treacy · ‎Jun 07, 2020

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.

Bill_French · ‎Jun 07, 2020

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;
}

Liz_Treacy · ‎Jun 07, 2020

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;
}

Liz_Treacy · ‎Jun 07, 2020

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')),
    });
}