Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 29 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Formula for Number to Text

14049 24
cancel
Showing results for 
Search instead for 
Did you mean: 
jowan_qupty
6 - Interface Innovator
6 - Interface Innovator

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?

24 Replies 24
Mattie_Johnson
4 - Data Explorer
4 - Data Explorer

Thank you so much , very helpful!

Sebastien_Munie
4 - Data Explorer
4 - Data Explorer

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.

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.

KPFS
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

KPFS_0-1711605490450.png

See the Screenshot the script works.