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?

Bill_French · ‎Jun 07, 2020

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

Marian_Kirstie_ · ‎Sep 02, 2020

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

Hady_Abdelnour · ‎Oct 12, 2020

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

Bill_French · ‎Oct 12, 2020

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

Hady_Abdelnour · ‎Oct 13, 2020

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

Traci_Franssen · ‎Jan 20, 2021

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

Issam_Chaouch · ‎Mar 01, 2021

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

Aaron_Buchinger · ‎Feb 22, 2022

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”

Liz_Treacy · ‎Feb 22, 2022

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!

Sean_Christense · ‎May 06, 2022

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