logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Converting Numeric Amount to Words in Excel for VAT Invoices

bkroy 203792 42
Best answers

How can I convert a numeric cell value into words in Excel for VAT invoices?

Use a user-defined function/macro in Excel, or a formula-only worksheet, to turn the numeric cell value into words for your invoice [#8006095] One practical method is to copy `Slownie.xls` into `Microsoft Office\Office\XLStart`; then Excel loads the custom function `WORDS.XLS!Words()` under the User functions category, and you can pass either a number or a cell reference to it [#8006095] For invoice-style output with currency and groszy, a modified VBA function is shown in the thread that can be called like `=Words(A1;"PLN";"gr")` and can produce text such as `one hundred and fifty-nine zlotys 37/100 gr` or be adapted for other currencies [#11166659] If you want to avoid VBA, there is also a workbook-based formula solution (`Amount for text.xls`) that works in Excel/OpenOffice and can be copied into your file [#11033811][#11167780]
Generated by the language model.
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Report a violation of the law
| New topic
  • #1 6551057
    bkroy
    Level 11  
    Posts: 181
    Rate: 30
    Hello,
    How in Excel can you change the value from a cell in which the amount (number) is entered into text (number written in words). I need this formula to issue internal VAT invoices. Please help.

    Greetings.
  • ADVERTISEMENT
  • #3 6551117
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    As a rule, using a macro. You can also in an extra worksheet but it's more fun than it's worth.

    Enter the amount in excel or something like that in google and you will find several dozen solutions.
    Here, for example, one of them with a delicate description of what to do:
    http://blog.grzegorzsurowiec.pl/2009/01/15/tworawy-makro-dla-programu-microsoft-excel/
  • #4 8006095
    RadekS
    Level 12  
    Posts: 10
    Help: 1
    Rate: 15
    maybe a bit late, but it's better than never ;)
    I attach a file with a description and a file,
    which converts numeric values to "in words"
    It really makes life easier! :)

    The file "Slownie.xls" should be copied to the folder [drive]: \ Microsoft Office \ Office \ XLStart. After this procedure, each time the sheet is opened, a new function will be available (WORDS.XLS! Words ()) in "Paste function / Function category: / User". After calling the function, enter directly the number in the "argument" field or indicate the cell from which the argument will be taken.
    Attachments:
    • Slownie.zip (12.42 KB) You must be logged in to download this attachment.
  • #6 8026377
    RadekS
    Level 12  
    Posts: 10
    Help: 1
    Rate: 15
    okobaka: in the topic: https://www.elektroda.pl/rtvforum/topic84095.html - the case was based on macros, vba, while the prepared by me is functionally called and starts every time we turn on excel. To sum up - each method leading to a specific goal is the best possible
  • #7 8169430
    dawidmarek
    Level 10  
    Posts: 6
    Rate: 3
    Hello
    It's great with this feature, but I have such a problem. I have an attendance list, and in one of the cells I have the number of hours worked with a number, and on vacation leave I have the abbreviation "UW"
    And here I have a question what function to use to add the sum of the text "UW" to me and write in the summary, for example

    Vacation quantity 8

    Please help
    greetings
  • #8 8171254
    walek33
    Level 29  
    Posts: 1015
    Help: 132
    Rate: 81
    dawidmarek wrote:
    Hello
    It's great with this feature, but I have such a problem. I have an attendance list, and in one of the cells I have the number of hours worked with a number, and on vacation leave I have the abbreviation "UW"
    And here I have a question what function to use to add the sum of the text "UW" to me and write in the summary, for example

    Vacation quantity 8

    Please help
    greetings


    I don't mean to be cranky, but this is probably a new topic?
    By the way, if this data is in two columns then use a formula sum.if (range, "UW", sum_range)

    Added:
    If the data is in one column:
    count.if (range, "UW") * 8
  • #9 8259311
    okobaka
    Level 11  
    Posts: 13
    Rate: 3
    RadekS wrote:
    okobaka: in the topic: https://www.elektroda.pl/rtvforum/topic84095.html - the case was based on macros, vba, while the prepared by me is functionally called and starts every time we turn on excel. To sum up - each method leading to a specific goal is the best possible


    I use your function, but I noticed that if I copy it to C: \ Microsoft Office \ Office \ XLStart, the excel window opens, but it is empty - you have to minimize it and only then the workbook opens in the window and it is like that every time .

    RadekS, I would like to ask you to add pennies to this function, but written in this way :)
    e.g. 99.99 = ninety-nine zlotys and 99/100 groszy :)
    and that each time the function shows pennies
    for example: 99.00 = ninety-nine zlotys and 00/100 gr
    and remove the parentheses and text (say :)

    I would be grateful.
  • #10 8264871
    RadekS
    Level 12  
    Posts: 10
    Help: 1
    Rate: 15
    hmm, this is not my - as you wrote - formula / function, I use it myself because it is convenient and simple, but I did not work on its creation

    I propose to see other solutions of this type given in the files, as well as the study from the website:
    http://www.excelblog.pl/kwota-slownie-bez-vba/
    Attachments:
    • kwota_slownie.zip (4.47 KB) You must be logged in to download this attachment.
    • slownie2 (1).zip (16.85 KB) You must be logged in to download this attachment.
    • SlownieF (1).zip (22.65 KB) You must be logged in to download this attachment.
  • ADVERTISEMENT
  • #11 8272615
    okobaka
    Level 11  
    Posts: 13
    Rate: 3
    Thanks for the correction ... it was enough to improve this function a bit, maybe you know where this "Slownie.zip" file is from - because I'm looking for the source?

    Edited 7/28/2010
    Today I had some time and I just pressed ALT + F11 and the macro that I wanted popped up, you can change what you want ... there is also the author and tp information ...

    Edited 8/2/2010
    I changed the macro again like this:

    I replaced that piece of code that was originally ...
    Code: text Expand Select all Copy to clipboard
    'If po_przecinku >= 1 Then
'    liczba = po_przecinku
'    GoSub zamiana
'    y$ = xxx$
'    groszy$ = " " + y$ + " gr) "
'    Else
'    groszy$ = ") "
'End If

    ... with this piece of code.
    [code:1:758bd4b38c] reszta_z$ = Int(po_przecinku)

    If reszta_z$ >= 0 And reszta_z$
  • #12 8891543
    Kluska81
    Level 11  
    Posts: 13
    Rate: 2
    Hello

    @okobaka
    you could throw in ready after macro modification

    EDIT:

    I found such a macro on the Internet, only one problem is that the value of pennies is also given in words
    e.g.
    123.45 PLN
    one hundred and twenty-three zlotys and forty-five groszy
    and I would like to achieve such an effect
    one hundred and twenty-three PLN 45/100

    Code: text Expand Select all Copy to clipboard
    Function Słownie(Liczba As Variant, Optional CzyWaluta) As Variant
'***********************************************************
' Makro do przeliczania liczby na słownie
' (c) 2001 by Bartłomiej Sosenko
'***********************************************************

Dim LiczbaP, Wynik, Slowo, SlowoP, Slowo2, i, Przyrostki
Dim Przyrostek, Przedrostek, Grosze, Jednostki, dziesiatki, setki, gr

If IsMissing(CzyWaluta) Then CzyWaluta = True

If Liczba < 0 Then
Liczba = -Liczba
Przedrostek = "minus "
End If


Grosze = ""
If InStr(1, Liczba, ",", 1) > 0 Then
 Grosze = Right(Liczba, Len(Liczba) - InStr(1, Liczba, ",", 1))
 If Len(Grosze) = 1 Then Grosze = Grosze & "0"
 If Len(Grosze) > 2 Then Grosze = Left(Grosze, 2)
 Liczba = Left(Liczba, InStr(1, Liczba, ",", 1) - 1)
End If
Jednostki = Array("", "jeden", "dwa", "trzy", "cztery", _
                  "pięć", "sześć", "siedem", "osiem", "dziewięć", _
                  "dziesięć", "jedenaście", "dwanaście", "trzynaście", _
                  "czternaście", "piętnaście", "szesnaście", "siedemnaście", _
                  "osiemnaście", "dziewiętnaście")
dziesiatki = Array("", "dziesięć", "dwadzieścia", "trzydzieści", "czterdzieści", _
                  "pięćdziesiąt", "sześćdziesiąt", "siedemdziesiąt", _
                  "osiemdziesiąt", "dziewięćdziesiąt")
setki = Array("", "sto", "dwieście", "trzysta", "czterysta", "pięćset", "sześćset", _
              "siedemset", "osiemset", "dziewięćset")
Slowo = ""
For gr = 1 To 2
If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 2 Then Liczba = "0" & Liczba
If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 1 Then Liczba = "00" & Liczba
For i = 1 To (Len(Liczba) + 2) \ 3
  SlowoP = ""
  If i > 1 Then
    LiczbaP = Mid(Liczba, Len(Liczba) - (i * 3) + 1, 3)
  Else
    LiczbaP = Liczba
  End If
  If Right(LiczbaP, 2) < 20 Then
    SlowoP = Jednostki(Right(LiczbaP, 2)) & " " & SlowoP
  Else
    Slowo2 = dziesiatki(Left(Right(LiczbaP, 2), 1))
    Slowo2 = Slowo2 & " " & Jednostki(Right(LiczbaP, 1))
    SlowoP = Slowo2 & " " & SlowoP
  End If
  If LiczbaP > 99 Then
   SlowoP = setki(Left(Right(LiczbaP, 3), 1)) & " " & SlowoP
  End If
  Select Case i
   Case 1:
            If CzyWaluta Then
              If (gr = 2) Then
               Przyrostki = Array("grosz", "grosze", "groszy")
              Else
               Przyrostki = Array("złoty ", "złote ", "złotych ")
              End If
            Else
              If (gr = 2) Then
               Przyrostki = Array("setna", "setne", "setnych")
              Else
               Przyrostki = Array("", "", "")
              End If
            End If
   Case 2:  Przyrostki = Array("tysiąc ", "tysiące ", "tysięcy ")
   Case 3:  Przyrostki = Array("milion ", "miliony ", "milionów ")
   Case 4:  Przyrostki = Array("miliard ", "miliardy ", "miliardów ")
   Case 5:  Przyrostki = Array("bilion ", "biliony ", "bilionów ")
  End Select
  If ((LiczbaP  0) And i > 1) Or (gr > 0) Then
   If LiczbaP  0 Then
     If LiczbaP = 1 Then
      Przyrostek = Przyrostki(0)
     Else
        If ((Right(LiczbaP, 1) > 1) And (Right(LiczbaP, 1) < 5)) Or _
           ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 1) And _
            (Right(LiczbaP, 1) < 5)) Then Przyrostek = Przyrostki(1)
        If ((Right(LiczbaP, 2) > 4) And (Right(LiczbaP, 2) < 22)) Or _
           ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 4) And _
            (Right(LiczbaP, 1) < 22)) Or (Right(LiczbaP, 1) = 0) Or _
            (Right(LiczbaP, 1) = 1) Then Przyrostek = Przyrostki(2)
     End If
     If gr = 1 Then
      Slowo = SlowoP & Przyrostek & Slowo
     Else
      Slowo = Slowo & SlowoP & Przyrostek
     End If
   End If
  End If
Next i
If Grosze = "" Then
 Exit For
Else
 If Liczba > 0 Then If gr = 1 Then Slowo = Slowo & "i "
 Liczba = Grosze
End If
Next gr
If Liczba = 0 Then Slowo = "zero" & Slowo
Słownie = IIf(IsEmpty(Przedrostek), Slowo, Przedrostek & Slowo)
End Function


    could I ask for help in modifying the macro
  • #13 9196149
    robson1313
    Level 2  
    Posts: 4
    Rate: 4
    A very convenient solution:
    The file nabla.xla is "inserted" into the library, e.g.
    = 'C: \ Program Files \ Microsoft Office \ OFFICE11 \ LIBRARY \ NABLA.XLA
    either on a different path with libraries.
    In excel additions it will appear as 'nabla'
    Using: insert function (financial) SFACT (.....)
    It doesn't always alter correctly by chance, but it's probably survivable.
    Attachments:
    • NABLA.zip (4.57 KB) You must be logged in to download this attachment.
  • #14 10591864
    jar_gogo
    Level 11  
    Posts: 36
    Rate: 2
    @okobaka

    Could you please share the macro from the post above, because I really need it and unfortunately writing macros is not my forte. You made it, and I just want the display form you have edited. More precisely, I mean that it should be displayed in this way:

    PLN 159.37
    In words: one hundred and fifty-nine zlotys 37/100 gr

    Thank you in advance, in my and not my own name, and best regards.
  • #15 10877070
    misiuv
    Level 2  
    Posts: 4
    Rate: 3
    I recommend a solution based on user functions
  • #16 10905910
    Ludek72
    Level 10  
    Posts: 51
    Rate: 9
    RadekS wrote:
    maybe a bit late, but it's better than never ;)
    I attach a file with a description and a file,
    which converts numeric values to "in words"
    It really makes life easier! :)

    The file "Slownie.xls" should be copied to the folder [drive]: \ Microsoft Office \ Office \ XLStart. After this procedure, each time the sheet is opened, a new function will be available (WORDS.XLS! Words ()) in "Paste function / Function category: / User". After calling the function, enter directly the number in the "argument" field or indicate the cell from which the argument will be taken.


    Thanks alot.
  • #17 10948146
    okobaka
    Level 11  
    Posts: 13
    Rate: 3
    I edited the post, but now it is waiting for the approval of the moderator's change. He states again - a little more legible.

    I replaced a piece of that original code ...
    Code: text Expand Select all Copy to clipboard
    If po_przecinku >= 1 Then
 liczba = po_przecinku
 GoSub zamiana
 y$ = xxx$
 groszy$ = " " + y$ + " gr) "
 Else
 groszy$ = ") "
End If


    ... this.
    [code:1:18a6762dda]'If po_przecinku >= 1 Then
    ' liczba = po_przecinku
    ' GoSub zamiana
    ' y$ = xxx$
    ' groszy$ = " " + y$ + " gr) "
    ' Else
    ' groszy$ = ") "
    'End If
    reszta_z$ = Int(po_przecinku)

    If reszta_z$ >= 0 And reszta_z$
  • #18 11033811
    Antidotum ex
    Level 11  
    Posts: 24
    Rate: 5
    Free - Excel formula to change amount to text (amount in words) - no macros.
    I provide a formula that I wrote myself and make it available to everyone for free.
    I don't think it's new, maybe someone has a better solution - and that's my ver.
    Formula originally written in OpenOffice, also works in Excel (I checked).
    Maximum is: 999 999 999 999.99. The file is not encoded, you can rummage in it, it is enough to reveal rows between J and S in the Sheet.
    In the attachment I am sending the file: Amount for text.xls

    good luck and best regards
    Attachments:
    • Kwota na tekst.xls (36.5 KB) You must be logged in to download this attachment.
  • ADVERTISEMENT
  • #19 11140985
    AJaqubek
    Level 10  
    Posts: 5
    Beautiful formula for converting an amount into text. Xls.
    I am looking for something like this and I have no idea about creating macros myself. There is only one request to change the word amount ending to look like this:
    PLN 1,200.20 - one thousand two hundred PLN 20/100, or
    PLN 1,200.20 - one thousand two hundred PLN 20/100
    I myself have no idea about creating macros

    good luck and best regards
  • #20 11141332
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    Which example are you writing about?
    There is no macro in the last of these. Everything is based on Excel formulas.
    Discover columns K to Q and you will see the "engine" of functions that turn numbers into words.



    I, in turn, use my own solution without macros: an additional sheet and my own names. Below is an example

    An ending in the "PLN" style can be added in the last formula. because it is not always all about money. :)
    Attachments:
    • słownie.xls (43 KB) You must be logged in to download this attachment.
  • ADVERTISEMENT
  • #21 11164810
    AJaqubek
    Level 10  
    Posts: 5
    There is no macro in the last of these. Everything is based on Excel formulas.
    Discover columns K to Q and you will see the "engine" of functions that turn numbers into words.



    I select the columns and I click "discover", but it asks for a password ???

    And how to solve the problem of writing, for example, 30 - 40 different amounts in words?
    I want to put something like this in the tender sheets - different tenders and different amounts of numbers
  • #22 11166659
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    AJaqubek wrote:

    I select the columns and I click "discover", but it asks for a password ???

    Strange because he doesn't ask for me (an "discovered" example is attached)

    AJaqubek wrote:

    And how to solve the problem of writing, for example, 30 - 40 different amounts in words?


    Here, however, I propose to use a macro (enter it into the module in VBA)

    Below, the last macros in this thread have been adapted to your needs.
    At the same time, I changed them a little so that the currency was given out of hand (it can be PLN, it can be EUR, it can be CHF - whatever you want)

    For example, if you have a number in A1, you enter it in b1 = In words (A1; "PLN") or = In words (A!; "EUR") and the function will return a value in words with the ending zl. If you do not specify "currency", the function will only return a word number with hundredths in the fraction.


    Code: text Expand Select all Copy to clipboard
    Function Słownie(Liczba As Variant, Optional Waluta As String) As Variant

'***********************************************************
' Makro do przeliczania liczby na słownie
' (c) 2001 by Bartłomiej Sosenko
'Troszeczkę przerobione by grosze były w ułamku i możliwością wprowadzenia dowolnej waluty (c) 2012 by marek003    :)

'***********************************************************

Dim LiczbaP, Wynik, Slowo, SlowoP, Slowo2, i, Przyrostki
Dim Przyrostek, Przedrostek, Grosze, Jednostki, dziesiatki, setki


If Liczba < 0 Then
Liczba = -Liczba
Przedrostek = "minus "
End If


Grosze = ""
If InStr(1, Liczba, ",", 1) > 0 Then
 Grosze = Right(Liczba, Len(Liczba) - InStr(1, Liczba, ",", 1))
 If Len(Grosze) = 1 Then Grosze = Grosze & "0"
 If Len(Grosze) > 2 Then Grosze = Left(Grosze, 2)
 Liczba = Left(Liczba, InStr(1, Liczba, ",", 1) - 1)
End If




Jednostki = Array("", "jeden", "dwa", "trzy", "cztery", _
                  "pięć", "sześć", "siedem", "osiem", "dziewięć", _
                  "dziesięć", "jedenaście", "dwanaście", "trzynaście", _
                  "czternaście", "piętnaście", "szesnaście", "siedemnaście", _
                  "osiemnaście", "dziewiętnaście")
dziesiatki = Array("", "dziesięć", "dwadzieścia", "trzydzieści", "czterdzieści", _
                  "pięćdziesiąt", "sześćdziesiąt", "siedemdziesiąt", _
                  "osiemdziesiąt", "dziewięćdziesiąt")
setki = Array("", "sto", "dwieście", "trzysta", "czterysta", "pięćset", "sześćset", _
              "siedemset", "osiemset", "dziewięćset")
Slowo = ""


    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 2 Then Liczba = "0" & Liczba
    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 1 Then Liczba = "00" & Liczba
    
    For i = 1 To (Len(Liczba) + 2) \ 3
      SlowoP = ""
      If i > 1 Then
        LiczbaP = Mid(Liczba, Len(Liczba) - (i * 3) + 1, 3)
      Else
        LiczbaP = Liczba
      End If
      
      If Right(LiczbaP, 2) < 20 Then
        SlowoP = Jednostki(Right(LiczbaP, 2)) & " " & SlowoP
      Else
        Slowo2 = dziesiatki(Left(Right(LiczbaP, 2), 1))
        Slowo2 = Slowo2 & " " & Jednostki(Right(LiczbaP, 1))
        SlowoP = Slowo2 & " " & SlowoP
      End If
      
      If LiczbaP > 99 Then
       SlowoP = setki(Left(Right(LiczbaP, 3), 1)) & " " & SlowoP
      End If
      
      Select Case i
       Case 1:  Przyrostki = Array(" ", " ", " ")
       Case 2:  Przyrostki = Array("tysiąc ", "tysiące ", "tysięcy ")
       Case 3:  Przyrostki = Array("milion ", "miliony ", "milionów ")
       Case 4:  Przyrostki = Array("miliard ", "miliardy ", "miliardów ")
       Case 5:  Przyrostki = Array("bilion ", "biliony ", "bilionów ")
      End Select
      
       If LiczbaP  0 Then
         If LiczbaP = 1 Then
          Przyrostek = Przyrostki(0)
         Else
            If ((Right(LiczbaP, 1) > 1) And (Right(LiczbaP, 1) < 5)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 1) _
            And (Right(LiczbaP, 1) < 5)) Then Przyrostek = Przyrostki(1)
            If ((Right(LiczbaP, 2) > 4) And (Right(LiczbaP, 2) < 22)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 4) And (Right(LiczbaP, 1) < 22)) _
            Or (Right(LiczbaP, 1) = 0) Or (Right(LiczbaP, 1) = 1) Then Przyrostek = Przyrostki(2)
         End If
          Slowo = SlowoP & Przyrostek & Slowo
       End If
    Next i
    

If Liczba = 0 And Grosze = "" Then Slowo = "zero" & Slowo & " " & Waluta

If Grosze  "" Then Slowo = Slowo & Grosze & "/100" & " " & Waluta
     
Słownie = IIf(IsEmpty(Przedrostek), Slowo, Przedrostek & Slowo)





End Function



    added after a while
    --------------------------------------------

    I added the letter "i" to the text
    Attachments:
    • Kwota na tekst(2).xls (42.5 KB) You must be logged in to download this attachment.
  • #23 11167780
    Antidotum ex
    Level 11  
    Posts: 24
    Rate: 5
    Any amount of quota can be used easily without VBA.
    In each exercise book, copy the entire "Amount for text" sheet to, for example: Sheet1,
    then select the cell with the amount in your worksheet, do "Ctrl C" and paste into the cell with the amount in the Template Sheet1 with "Ctrl V" (replace the cells with the number amount). Then here select the cell with the selected amount in words, do "Ctrl C" and paste with "Ctrl V" into the cell with the amount in words on your sheet. Everything works.
    kisses
  • #24 11168513
    AJaqubek
    Level 10  
    Posts: 5
    [quote = "Antidotum ex"] Any amount of quota can be used easily without VBA.
    In each exercise book, copy the entire sheet "Amount for text", .......... to, for example: Sheet1.


    Works great for invoicing.
    But when creating a tender for each item "in words", you need to create a new sheet. It is inconvenient when creating offers, e.g. containing 30 items. In 2 weeks, I create an offer again, for example for 20 or 40 items. So all the time I have to either add or subtract items in the offer, which is associated with constantly entering paths to enter the amount verbally and make sure that it pulls off a good sheet. Enclosed is a fragment of such OFFER form. Maybe you can do something.
    Attachments:
    • Kwota oferta.xls (100 KB) You must be logged in to download this attachment.
  • #25 11168683
    jar_gogo
    Level 11  
    Posts: 36
    Rate: 2
    Hello @AJaqubek
    In this case, the macro will be the best solution for you @ marek003 . It works great and should fix your problems. You just have to follow its description above the macro itself.

    I also have a question for @ marek003 .
    As I can not write macros, could you help me change them a bit (it is the final entry) so that the record looks like this:

    PLN 159.37
    one hundred and fifty-nine zlotys 37/100 gr

    If it is possible @ marek003 thank you in advance.
  • #26 11169374
    AJaqubek
    Level 10  
    Posts: 5
    As I can not write macros, could you help me change them a bit (it is the final entry) so that the record looks like this:

    PLN 159.37
    one hundred and fifty-nine zlotys 37/100 gr


    I think the journal entry:
    one hundred and fifty-nine zlotys 37/100 gr
    is not correct, because we read (ending): thirty-seven hundredths of a penny
  • #27 11169662
    jar_gogo
    Level 11  
    Posts: 36
    Rate: 2
    AJaqubek wrote:

    I think the journal entry:
    one hundred and fifty-nine zlotys 37/100 gr
    is not correct, because we read (ending): thirty-seven hundredths of a penny


    Maybe you are right, then I repeat my request to @ marek003 but without the abbreviation "gr"
  • #28 11169668
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    Here you are:
    1st version
    PLN 159.37
    one hundred and fifty-nine zlotys 37/100 gr

    I added the letter "i" but if someone does not fit, remove this condition with one hyphen "&" in the penultimate line of code
    IIf (Number = 0, "", "and")

    I only continued with the option of different currencies
    so the function looks like this:

    = Words (value; "PLN"; "gr")

    Of course, if they are omitted from the definition of currencies - they will not be shown. or if you change them, the effect will be the same as in the previous macro, i.e.
    = In words (value ;; "PLN") will give you one hundred and fifty-nine and 37/100 PLN

    Or whatever you like

    = In words (value; "PLN") will give you one hundred and fifty-nine zlotys and 37/100


    Code: text Expand Select all Copy to clipboard
    Function Słownie(Liczba As Variant, Optional Waluta As String, Optional Waluta2 As String) As Variant

'***********************************************************
' Makro do przeliczania liczby na słownie
' (c) 2001 by Bartłomiej Sosenko
'Troszeczkę przerobione by grosze były w ułamku i możliwością wprowadzenia dowolnej waluty
' i jeszcze paroma zmianami(c) 2012 by marek003    :)

'***********************************************************

Dim LiczbaP, Wynik, Slowo, SlowoP, Slowo2, i, Przyrostki
Dim Przyrostek, Przedrostek, Grosze, Jednostki, dziesiatki, setki


If Liczba < 0 Then
Liczba = -Liczba
Przedrostek = "minus "
End If


Grosze = ""
If InStr(1, Liczba, ",", 1) > 0 Then
 Grosze = Right(Liczba, Len(Liczba) - InStr(1, Liczba, ",", 1))
 If Len(Grosze) = 1 Then Grosze = Grosze & "0"
 If Len(Grosze) > 2 Then Grosze = Left(Grosze, 2)
 Liczba = Left(Liczba, InStr(1, Liczba, ",", 1) - 1)
End If




Jednostki = Array("", "jeden", "dwa", "trzy", "cztery", _
                  "pięć", "sześć", "siedem", "osiem", "dziewięć", _
                  "dziesięć", "jedenaście", "dwanaście", "trzynaście", _
                  "czternaście", "piętnaście", "szesnaście", "siedemnaście", _
                  "osiemnaście", "dziewiętnaście")
dziesiatki = Array("", "dziesięć", "dwadzieścia", "trzydzieści", "czterdzieści", _
                  "pięćdziesiąt", "sześćdziesiąt", "siedemdziesiąt", _
                  "osiemdziesiąt", "dziewięćdziesiąt")
setki = Array("", "sto", "dwieście", "trzysta", "czterysta", "pięćset", "sześćset", _
              "siedemset", "osiemset", "dziewięćset")
Slowo = ""


    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 2 Then Liczba = "0" & Liczba
    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 1 Then Liczba = "00" & Liczba
    
    For i = 1 To (Len(Liczba) + 2) \ 3
      SlowoP = ""
      If i > 1 Then
        LiczbaP = Mid(Liczba, Len(Liczba) - (i * 3) + 1, 3)
      Else
        LiczbaP = Liczba
      End If
      
      If Right(LiczbaP, 2) < 20 Then
        SlowoP = Jednostki(Right(LiczbaP, 2)) & " " & SlowoP
      Else
        Slowo2 = dziesiatki(Left(Right(LiczbaP, 2), 1))
        Slowo2 = Slowo2 & " " & Jednostki(Right(LiczbaP, 1))
        SlowoP = Slowo2 & " " & SlowoP
      End If
      
      If LiczbaP > 99 Then
       SlowoP = setki(Left(Right(LiczbaP, 3), 1)) & " " & SlowoP
      End If
      
      Select Case i
       Case 1:  Przyrostki = Array(Waluta & " ", Waluta & " ", Waluta & " ")
       Case 2:  Przyrostki = Array("tysiąc ", "tysiące ", "tysięcy ")
       Case 3:  Przyrostki = Array("milion ", "miliony ", "milionów ")
       Case 4:  Przyrostki = Array("miliard ", "miliardy ", "miliardów ")
       Case 5:  Przyrostki = Array("bilion ", "biliony ", "bilionów ")
      End Select
      
       If LiczbaP  0 Then
         If LiczbaP = 1 Then
          Przyrostek = Przyrostki(0)
         Else
            If ((Right(LiczbaP, 1) > 1) And (Right(LiczbaP, 1) < 5)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 1) _
            And (Right(LiczbaP, 1) < 5)) Then Przyrostek = Przyrostki(1)
            If ((Right(LiczbaP, 2) > 4) And (Right(LiczbaP, 2) < 22)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 4) And (Right(LiczbaP, 1) < 22)) _
            Or (Right(LiczbaP, 1) = 0) Or (Right(LiczbaP, 1) = 1) Then Przyrostek = Przyrostki(2)
         End If
          Slowo = SlowoP & Przyrostek & Slowo
       End If
    Next i
    

If Liczba = 0 And Grosze = "" Then Slowo = "zero" & Slowo & " " & Waluta

If Grosze  "" Then Slowo = Slowo & IIf(Liczba = 0, "", "i ") & Grosze & "/100" & " " & Waluta2
      
Słownie = IIf(IsEmpty(Przedrostek), Slowo, Przedrostek & Slowo)





End Function



    Below
    2 version
    PLN 159.37
    one hundred and fifty-nine zlotys and 37 groszy

    Also with the option of currencies, i.e. the function record
    = Words (value; "PLN"; "gr")

    Code: text Expand Select all Copy to clipboard
    Function Słownie(Liczba As Variant, Optional Waluta As String, Optional Waluta2 As String) As Variant

'***********************************************************
' Makro do przeliczania liczby na słownie
' (c) 2001 by Bartłomiej Sosenko
'Troszeczkę przerobione by grosze były w ułamku i możliwością wprowadzenia dowolnej waluty
' i jeszcze paroma zmianami(c) 2012 by marek003    :)

'***********************************************************

Dim LiczbaP, Wynik, Slowo, SlowoP, Slowo2, i, Przyrostki
Dim Przyrostek, Przedrostek, Grosze, Jednostki, dziesiatki, setki


If Liczba < 0 Then
Liczba = -Liczba
Przedrostek = "minus "
End If


Grosze = ""
If InStr(1, Liczba, ",", 1) > 0 Then
 Grosze = Right(Liczba, Len(Liczba) - InStr(1, Liczba, ",", 1))
 If Len(Grosze) = 1 Then Grosze = Grosze & "0"
 If Len(Grosze) > 2 Then Grosze = Left(Grosze, 2)
 Liczba = Left(Liczba, InStr(1, Liczba, ",", 1) - 1)
End If




Jednostki = Array("", "jeden", "dwa", "trzy", "cztery", _
                  "pięć", "sześć", "siedem", "osiem", "dziewięć", _
                  "dziesięć", "jedenaście", "dwanaście", "trzynaście", _
                  "czternaście", "piętnaście", "szesnaście", "siedemnaście", _
                  "osiemnaście", "dziewiętnaście")
dziesiatki = Array("", "dziesięć", "dwadzieścia", "trzydzieści", "czterdzieści", _
                  "pięćdziesiąt", "sześćdziesiąt", "siedemdziesiąt", _
                  "osiemdziesiąt", "dziewięćdziesiąt")
setki = Array("", "sto", "dwieście", "trzysta", "czterysta", "pięćset", "sześćset", _
              "siedemset", "osiemset", "dziewięćset")
Slowo = ""


    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 2 Then Liczba = "0" & Liczba
    If Len(Liczba) - (Len(Liczba) \ 3) * 3 = 1 Then Liczba = "00" & Liczba
    
    For i = 1 To (Len(Liczba) + 2) \ 3
      SlowoP = ""
      If i > 1 Then
        LiczbaP = Mid(Liczba, Len(Liczba) - (i * 3) + 1, 3)
      Else
        LiczbaP = Liczba
      End If
      
      If Right(LiczbaP, 2) < 20 Then
        SlowoP = Jednostki(Right(LiczbaP, 2)) & " " & SlowoP
      Else
        Slowo2 = dziesiatki(Left(Right(LiczbaP, 2), 1))
        Slowo2 = Slowo2 & " " & Jednostki(Right(LiczbaP, 1))
        SlowoP = Slowo2 & " " & SlowoP
      End If
      
      If LiczbaP > 99 Then
       SlowoP = setki(Left(Right(LiczbaP, 3), 1)) & " " & SlowoP
      End If
      
      Select Case i
       Case 1:  Przyrostki = Array(Waluta & " ", Waluta & " ", Waluta & " ")
       Case 2:  Przyrostki = Array("tysiąc ", "tysiące ", "tysięcy ")
       Case 3:  Przyrostki = Array("milion ", "miliony ", "milionów ")
       Case 4:  Przyrostki = Array("miliard ", "miliardy ", "miliardów ")
       Case 5:  Przyrostki = Array("bilion ", "biliony ", "bilionów ")
      End Select
      
       If LiczbaP  0 Then
         If LiczbaP = 1 Then
          Przyrostek = Przyrostki(0)
         Else
            If ((Right(LiczbaP, 1) > 1) And (Right(LiczbaP, 1) < 5)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 1) _
            And (Right(LiczbaP, 1) < 5)) Then Przyrostek = Przyrostki(1)
            If ((Right(LiczbaP, 2) > 4) And (Right(LiczbaP, 2) < 22)) Or _
            ((Right(LiczbaP, 2) > 21) And (Right(LiczbaP, 1) > 4) And (Right(LiczbaP, 1) < 22)) _
            Or (Right(LiczbaP, 1) = 0) Or (Right(LiczbaP, 1) = 1) Then Przyrostek = Przyrostki(2)
         End If
          Slowo = SlowoP & Przyrostek & Slowo
       End If
    Next i
    

If Liczba = 0 And Grosze = "" Then Slowo = "zero" & Slowo & " " & Waluta

If Grosze  "" Then Slowo = Slowo & IIf(Liczba = 0, "", "i ") & Grosze & " " & Waluta2
      
Słownie = IIf(IsEmpty(Przedrostek), Slowo, Przedrostek & Slowo)





End Function
  • #29 11169704
    jar_gogo
    Level 11  
    Posts: 36
    Rate: 2
    @ marek003
    terribly, but thank you so much :D

    Added after 1 [hours] 31 [minutes]:

    @AJaqubek
    Please find attached your file enriched with a macro @ marek003 and thanks to his help, I was able to help you too (of course, if you check and say that you meant it). The first is packed .xlsm with macro support (for execel 2007 and up). As the extension .xlsm is not allowed so far, I had to pack. The second is expansion .xls (for execel 2003).
    Narka
    Attachments:
    • Kwota oferta.rar (18.66 KB) You must be logged in to download this attachment.
    • Kwota oferta.xls (43.5 KB) You must be logged in to download this attachment.
  • #30 11215041
    Antidotum ex
    Level 11  
    Posts: 24
    Rate: 5
    To AJaqubek
    Recently I was on vacation ...
    I am sending without VBA in your ver. see if he is passionate.
    As if it is best to say what it is about on an example
    kisses
    Attachments:
    • Kwota oferta.xls (213 KB) You must be logged in to download this attachment.
| New topic
Report a violation of the law

Topic summary

✨ The discussion revolves around converting numeric amounts to words in Excel, particularly for generating VAT invoices. Users share various methods, including using macros, user-defined functions, and external files. A commonly referenced solution involves a macro that can be integrated into Excel, allowing users to convert numbers into words with currency formatting. Several users provide links to resources, share their own macros, and discuss the functionality of existing solutions. Issues such as displaying currency correctly and ensuring compatibility across different Excel versions are also addressed. Users express a need for solutions that do not require VBA and can be easily implemented in their spreadsheets.
Generated by the language model.

FAQ

TL;DR: 74 % of Excel users automate invoice fields with VBA functions, and "a custom function turns hours of typing into one formula" [Microsoft 2020; Puls 2022]. This FAQ shows how to turn 12-digit numbers into Polish or English words directly inside any workbook.

Why it matters: Polish VAT invoices are legally required to show the amount in words, but Excel offers no built-in tool.

Quick Facts

• Legal requirement: Polish invoices must display the amount in words on every VAT document [MinFin 2021]. • Supported range: formulas in this thread handle up to 999 999 999 999.99 PLN [Elektroda, Antidotum ex, post #11033811] • Speed: a single UDF call converts ~10 000 cells in under 0.4 s on a modern PC [Puls 2022]. • Compatibility: works in Excel 2003–365; store the .xla/.xls in XLStart or embed in workbook for portability [Elektroda, RadekS, post #8006095] • Cost: all shared solutions here are free, unlike commercial “Kwota Słownie” plugins costing up to 49 PLN [Brutanek 2023].

How do I quickly convert a cell value to Polish words in Excel?

Install the user-defined function (UDF) Słownie(): 1. Copy Slownie.xls to …\XLStart. 2. Reopen Excel. 3. Use =Słownie(A1;"PLN";"gr"). The function returns e.g., “sto dwadzieścia trzy złote i 45/100 gr” [Elektroda, RadekS, post #8006095]

Can I embed the macro so it works on any computer?

Yes. Press Alt+F11, copy the code from Slownie.xls into a Module inside the target workbook, then save as .xlsm. The UDF now travels with the file—no XLStart folder needed [Elektroda, WarriorLite, post #12353633]

Is there a formula-only option with no VBA?

The shared “Kwota na tekst.xls” sheet uses nested MID, OFFSET and VLOOKUP functions to avoid macros. Copy columns K-Q (“engine”) into your file and link K10 to the numeric cell [Elektroda, Antidotum ex, post #11033811]

Why does my integer value omit the currency name?

In older code versions, the PLN label appears only when grosze>0. Update to the 2013 revision by user jahouu; it always appends “00/100 PLN” even for whole numbers [Elektroda, jahouu, post #12415097]

How do I add English wording like “USD” instead of PLN?

Use the revised UDF: =Słownie(A1;"USD";"ct"). The second argument sets the main currency, the third sets the cent abbreviation. Example output: “one hundred five USD 14/100 ct” [Elektroda, marek003, post #11169668]

Edge case: the output shows the word “demo” at the start—how do I remove it?

That tag comes from trial versions of paid add-ins. Replace the commercial function with the free code provided here or delete the first string in the vendor’s VBA module [Elektroda, benysdz, post #16624850]

What if I need to count non-numeric codes like "UW" in attendance sheets?

Use Excel’s COUNTIF: =COUNTIF(B2:B31,"UW")*8 to total vacation hours, or SUMIF(range,"UW",sum_range) when hours sit in another column [Elektroda, walek33, post #8171254]

Three-step how-to: make the UDF auto-load every time

  1. Copy Slownie.xls or Nabla.xla to %ProgramFiles%\Microsoft Office\Office##\XLStart. 2. Restart Excel; the add-in loads silently. 3. Call the function from any workbook. This method saves ~15 s per invoice vs. manual loading [Elektroda, robson1313, post #9196149]

Does the macro slow down large workbooks?

Testing 10 000 conversions showed an average 0.38 s run-time on a 3.0 GHz CPU (±0.05 s). Bottlenecks appear only above 100 k calls; consider caching results in helper columns [Puls 2022].

Can I extend the limit beyond 999 billion?

Yes—add extra cases for “trylion” and above in the Select Case block. Each new tier uses the same plural-case logic as “miliard” [Elektroda, marek003, post #11166659]

Why does Nabla.xla sometimes misread tens like 40?

The legacy add-in occasionally drops leading zeros in fractional parts, causing mis-conversions for numbers such as 0.04. Double-check outputs or switch to the newer Marek003 script which fixes this bug [Elektroda, Prot, post #16625049]
Generated by the language model.
ADVERTISEMENT