Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 15, 2024

Numbers2Wordsλ in Excel 365

Numbers2Wordsλ

Background

There have been a few discussions in the past on this forum that offered lengthy solutions to this task.  My approach to this task attempts to shortcut the problem by using the seldom used BAHTTEXT function.  It recently occurred to me BAHTTEXT converts a number to Thai and uses baht(s) and satang (fractional unit).  This coupled with the recently released to current channel TRANSLATE was the source of inspiration.

The Plan

Numbers >> Thai (BAHTTEXT) >> (Desired language - TRANSLATE) >> some cleanup with Regex

Observations

This method is very accurate up until about 10,000 and then some anomalies creep in:

From what I gather this appears to be a result of the way BAHTTEXT converts numbers to words because a cursory search for "Ten thousand" in Thai returned a translation.  There doesn't seem to be any pattern in that the function cannot produce words for 100,000 but can handle 211,111 without a problem.  I think it can be accounted for but then I might as well change the whole approach and not use BAHTTEXT and TRANSLATE.

Pro

Numbers2Wordsλ is somewhat simple and reads easy

Very accurate to 9,999 and capable of handling all languages used with TRANSLATE and supports a lot of popular currencies.

Con

Anomalies beyond 10,000.  BAHTTEXT does not seem to do well with 1s and 0s.  Some languages may not translate Thai to Words (Japanese, for example).

This method relies on TRANSLATE which is a service function that can be throttled with overuse.

This method calculates about as fast as cells with Python objects.

Discussion

I leave the function the below. Also, interested in alternative solutions to multiple replacements with REGEXREPLACE. From my research, there doesn't appear to be an elegant way to do it all in one go.

// --- Workbook module --- 

//Convert numbers to words by specifying language and currency.
Numbers2Wordsλ = LAMBDA(numbers, language, currency,
    LET(
//Translate provided numbers to Thai and then to specified language.
        Thai, BAHTTEXT(numbers),
        translated, TRANSLATE(Thai, "th", language),
//Identify the row for the selected currency in the List matrix.    
        i, XMATCH(currency, Country),
//Determine if replacement for whole number is singular or plural with 'a'.
//Do the same for fractional units with 'b'
        replace_a, INDEX(List, i, (INT(numbers) > 1) + 2),
        replace_b, INDEX(List, i, (numbers - INT(numbers) > 0.01) + 4),
//Replace Thai currency words: baht(s), satang, and net.
        return, REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(translated,
                "bahts?",replace_a ,, 1),"satang.?",replace_b,,1)," net","",,1),
        IF(language="th",translated,return)));

//Supported language codes: 
//https://learn.microsoft.com/en-us/azure/ai-services/Translator/language-support#translation 

//Supported currencies 
List = { 
    "Country", "Currency", "Currency (plural)", "Fractional", "Fractional (plural)"; 
    "AUD", "dollar", "dollars", "cent", "cents"; 
    "CAD", "dollar", "dollars", "cent", "cents"; 
    "CHF", "franc", "francs", "rappen", "rappen"; 
    "CHN", "yuan", "yua", "fen", "fen"; 
    "CLP", "peso", "pesos", "centavo", "centavos"; 
    "DKK", "krone", "kroner", "Øre", "Øre"; 
    "DZD", "dinar", "Dinars", "centime", "centimes"; 
    "EUR", "euro", "euros", "euro cent", "euro cents"; 
    "GBP", "pound", "pounds", "penny", "pence"; 
    "ILS", "shekel", "shekels", "agora", "agorot"; 
    "INR", "rupee", "rupees", "paisa", "paise"; 
    "JPN", "yen", "yen", "sen", "sen"; 
    "MXN", "peso", "pesos", "centavo", "centavos"; 
    "NOK", "krone", "kroner", "Øre", "Øre"; 
    "NZD", "dollar", "dollars", "cent", "cents"; 
    "RUB", "ruble", "rubles", "Kopeck", "Kopecks"; 
    "TH", "raht", "bahts", "satang", "satang"; 
    "TRY", "lira", "lire", "kuruş", "kuruş"; 
    "USD", "dollar", "dollars", "cent", "cents"; 
    "XAF", "krone", "kroner", "centime", "centimes"; 
    "XCD", "dollar", "dollars", "cent", "cents";
    "XOF", "franc", "francs", "centime", "centimes";
    "XPF", "franc", "francs", "centime", "centimes";
    "ZAR", "rand", "rand", "cent", "cents"}; 

Country = TAKE(List,,1); 
CurrencyName = CHOOSECOLS(List,2); 
CurrencyPl = CHOOSECOLS(List,3); 
Fractional = CHOOSECOLS(List,4); 
FractionalPl = TAKE(List,,-1); 

 

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    I am wonder about thinking that, why Microsoft team do not include SpellNumber() as built-in function?

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      Good question. I guess that's quite hard in maintenance function. They need to support all combinations of supported with Office languages with currencies used on the market and taking into account quite specific formatting used in some countries.

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor

        At least US dollar currency format can be done as all the countries use USD currency for international payments (LC opening) and accounting department keep records on Excel.

  • Nice idea. I didn't dig the function itself, couple of comments on results

    • layout is different, we have both "few dollars AND some cents", or "few dollars some cents" (without and)
    • actually we have to use double translation, e.g.
    =TRANSLATE( Numbers2Wordsλ(amount,"en","RUB"),,"ru")

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor

      I realize the flaws of the approach in relying on BAHTTEXT and the translation that may/may not include AND before the fractional unit. It made me think - "What is the correct way to say it out loud?"  If one is to factor for all the anomalies, then the approach (which becomes messy!) might as well be different was my thinking.  I had to take into account baht(s), satang, and "net" which often follows an amount.

      Mainly, the reason for sharing a flawed Lambda is to put it into existence so it may stimulate discussion.  I've not seen anyone else attempt this yet so far. Here it is and here's the good and the bad!

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        This function, perhaps with small adjustments, could be quite useful, especially if we shift from currency to currency. If only not to follow strict guidance's  on corporate or like levels. Even for one language it could be

        one dollar only

        one and 00/100 dollars

        one dollar and zero cents

        etc., etc.,...

         

Resources