Formula for Number to Text

[jowan_qupty]

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?

[Mattie_Johnson]

Thank you so much , very helpful!

[Sebastien_Munie]

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.

[Tyler_Surrency]

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.

[Sebastien_Munie]

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

[KPFS]

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.