Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Jun 28, 2019
Solved

Excel NumToWords Formula

To revel my 20th week as a member of this EXCELlent Community, I hereby share my NumToWords formula. Delve into it in the attached file and please apprise me of your thoughts thereon.
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Aug 28, 2020

    Jay8642, your request is my command! Note that I simply edited the formulas for these defined names: 

    1. CurName

    =IF(INT(IntNum)=0,"","Pound"&LEFT("s",INT(IntNum)>1))

     

    2. DecName

    ="Pen"&IF(SIGN(DecNum-1),"ce","ny")

     

    Finally, I renamed NumToDollars as NumToPounds. To satisfy your curiosity on the process of constructing the formula, you can read my articles on NumToWords and NumToDollars. The snapshot below illustrates the use of the NumToPounds formula: 

    The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.

foxfossil's avatar
foxfossil
Copper Contributor
Dec 05, 2020

Twifoo 

Great formula!

BUT ;)>

in German figures it is "Vierundzwanzig" insted of "Twenty-four". The digits of a number in the range 21...99 are switched. Can you help me on with an update for your formula to German numbers? The words are no problem, just the algorithm.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Dec 05, 2020

    foxfossil 

    I'm sorry. I don't know how to translate the words from English to German. I understand the currency in Germany now is Euros. Don't you want the currency in words to be translated to Euros instead?

    • foxfossil's avatar
      foxfossil
      Copper Contributor
      Dec 06, 2020

      Twifoo 

      thanks for your quick reply!

      No, it is not about translation. I can handle that in your formula.Using the names manager in Excel I can translate the words in SfxList, TxtList1 and TxtList2.

      My problem lies in the syntax of German numbers:

      Where English has "Twenty-four", "Thirty-one", German language switches the digits to "Vierundzwanzig", "Einunddreissig". ("Fourandtwenty", "Oneandthirty").

      As far as I understand your algorithm, I would have to swap the order of OneTxt and TenTxt in IntTxt!?

      "Twenty-" in TxtList2 would translate to "undzwanzig" ("andtwenty") etc.

      Best regards and have a good weekend! ;)>

Resources