Number in words c. How to write an amount, number, numbers in words in Excel. Description of working with the function

A very common task is to write numbers in words in Excel. There is no built-in function in Excel yet, so we can create a custom function that will replace numbers with text.

As a rule, this is required in trade, accounting and other areas where settlements with funds are made. Usually it is necessary to translate the amount in rubles and kopecks in words, as in the picture (first example).

Let's say we do some calculations in the table and get a total amount in rubles of 1526.23

We need to enter this figure in rubles and it is advisable to indicate kopecks as well. To do this, we will create a special universal user function that will look like this:

Propis(Amount ;Money ;lang ;Prec )

Money - here the type of currency is indicated, you can specify rubles, dollars and euros (“RUB”, “USD”, “EUR”) - the currency must be indicated in quotation marks.

lang is the language in which you need to display the amount, two languages ​​are available: English and Russian (“EN”, “RU”) - also indicated in quotes

Prec — show (1) or not show (0) the fractional part

Thus, you can write the amount in rubles, dollars or euros in Russian or English letters along with the fractional part, and depending on the number, the correct ending will be inserted, for example 2 rubles, 8 rubles, 1 ruble and so on.

To create a custom Propis function, you need to copy the code given below, then click ALT+F11 to open VBA, add a new empty module via the menu Insert - Module and paste the copied code there

Custom sum function macro in words

Function Propis(Amount As String, Optional Money As String = "RUB", Optional lang As String = "RU", Optional Prec As Integer = 1) Dim whole As Double Amount = Replace(Amount, "-", Application.International( xlDecimalSeparator)) Amount = Replace(Amount, ".", Application.International(xlDecimalSeparator)) Amount = Replace(Amount, ",", Application.International(xlDecimalSeparator)) Sum = WorksheetFunction.Round(CDbl(Amount), 2) Money = UCase(Money) lang = UCase(lang) whole = Int(Sum) fraq = Format(Round((Sum - whole) * 100), "00") Select Case Class(whole, 1) + Class(whole, 2) * 10 Case 1, 21, 31, 41, 51, 61, 71, 81, 91 w_rus_r = "ruble" w_rus_d = "dollar" w_rus_e = "euro" w_en_r = "rubles" w_en_d = "dollars" w_en_e = " euro" Case 2, 3, 4, 22, 23, 24, 32, 33, 34, 42, 43, 44, 52, 53, 54, 62, 63, 64, 72, 73, 74, 82, 83, 84 , 92, 93, 94 w_rus_r = "ruble" w_rus_d = "dollar" w_rus_e = "euro" w_en_r = "rubles" w_en_d = "dollars" w_en_e = "euro" Case Else w_rus_r = "ruble" w_rus_d = "dollars" w_rus_e = "euro" w_en_r = "rubles" w_en_d = "dollars" w_en_e = "euro" End Select Select Case fraq Case 1, 21, 31, 41, 51, 61, 71, 81, 91 f_rus_r = "kopeck" f_rus_d = "cent " f_rus_e = "cent" f_rus_p = "hundredth" f_en_r = "kopecks" f_en_d = "cents" f_en_e = "cents" f_en_e = "cents" Case 2, 3, 4, 22, 23, 24, 32, 33, 34, 42, 43, 44, 52, 53, 54, 62, 63, 64, 72, 73, 74, 82, 83, 84, 92, 93, 94 f_rus_r = "kopecks" f_rus_d = "cent" f_rus_e = "cent" f_en_r = "kopecks" f_en_d = "cents" f_en_e = "cents" Case Else f_rus_r = "kopecks" f_rus_d = "cents" f_rus_e = "cents" f_en_r = "kopecks" f_en_d = "cents" f_en_e = "cents" End Select If Prec = 0 Then fraq = "" f_rus_r = "" f_rus_d = "" f_rus_e = "" f_en_r = "" f_en_d = "" f_en_e = "" End If If lang = "RU" Then Select Case Money Case "RUB" Out = ScriptRus(whole) & " " & w_rus_r & " " & fraq & " " & f_rus_r Case "USD" Out = ScriptRus(whole) & " " & w_rus_d & " " & fraq & " " & f_rus_d Case "EUR" Out = ScriptRus(whole) & " " & w_rus_e & " " & fraq & " " & f_rus_e End Select End If If lang = "EN" Then Select Case Money Case "RUB" Out = ScriptEng(whole) & " " & w_en_r & " " & fraq & " " & f_en_r Case "USD" Out = ScriptEng(whole) & " " & w_en_d & " " & fraq & " " & f_en_d Case "EUR" Out = ScriptEng(whole) & " " & w_en_e & " " & fraq & " " & f_en_e End Select End If Propis = WorksheetFunction.Trim(Out) End Function Private Function Class(m, i) Class = Int(Int(m - (10 ^ i) * Int(m / (10 ^ i))) / 10 ^ (i - 1)) End Function Private Function ScriptRus(n As Double) As String Dim Nums1, Nums2, Nums3, Nums4 As Variant Nums1 = Array("", "one", "two " , "three", "four", "five", "six", "seven", "eight", "nine") Nums2 = Array("", "ten", "twenty", "thirty", "forty ", "fifty", "sixty", "seventy", "eighty", "ninety") Nums3 = Array("", "one hundred", "two hundred", "three hundred", "four hundred", "five hundred", " six hundred ", "seven hundred", "eight hundred", "nine hundred") Nums4 = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine") Nums5 = Array("ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen" ") If n = 0 Then ScriptRus = "Zero" Exit Function End If ed = Class(n, 1) dec = Class(n, 2) sot = Class(n, 3) tys = Class(n, 4) dectys = Class(n, 5) sottys = Class(n, 6) mil = Class(n, 7) decmil = Class(n, 8) sotmil = Class(n, 9) mlrd = Class(n, 10) If mlrd > 0 Then Select Case mlrd Case 1 mlrd_txt = Nums1(mlrd) & "billion" Case 2, 3, 4 mlrd_txt = Nums1(mlrd) & "billion" Case 5 To 20 mlrd_txt = Nums1(mlrd) & "billion" End Select End If If (sotmil + decmil + mil) > 0 Then sotmil_txt = Nums3(sotmil) Select Case decmil Case 1 mil_txt = Nums5(mil) & "millions" GoTo www Case 2 To 9 decmil_txt = Nums2(decmil) End Select Select Case mil Case 1 mil_txt = Nums1(mil) & "million" Case 2, 3, 4 mil_txt = Nums1(mil) & "million" Case 0, 5 To 20 mil_txt = Nums1(mil) & "million" End Select End If www: sottys_txt = Nums3(sottys) Select Case dectys Case 1 tys_txt = Nums5(tys) & "thousands" GoTo eee Case 2 To 9 dectys_txt = Nums2(dectys) End Select Select Case tys Case 0 If dectys > 0 Then tys_txt = Nums4(tys) & "thousands" Case 1 tys_txt = Nums4(tys) & "thousands" Case 2, 3, 4 tys_txt = Nums4(tys) & "thousands" Case 5 To 9 tys_txt = Nums4(tys) & "thousands" End Select If dectys = 0 And tys = 0 And sottys<>0 Then sottys_txt = sottys_txt & " thousand " eee: sot_txt = Nums3(sot) Select Case dec Case 1 ed_txt = Nums5(ed) GoTo rrr Case 2 To 9 dec_txt = Nums2(dec) End Select ed_txt = Nums1(ed) rrr: ScriptRus = mlrd_txt & sotmil_txt & decmil_txt & mil_txt & sottys_txt & dectys_txt & tys_txt & sot_txt & dec_txt & ed_txt ScriptRus = UCase(Left(ScriptRus, 1)) & LCase(Mid(ScriptRus, 2, Len(ScriptRus) - 1))End Function Private Function ScriptEng(ByVal Number As Double) Dim BigDenom As String, Temp As String Dim Count As Integer ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " strAmount = Trim(Str(Int(Number))) Count = 1 Do While strAmount<>"" Temp = GetHundreds(Right(strAmount, 3)) If Temp<>"" Then BigDenom = Temp & Place(Count) & BigDenom If Len(strAmount) > 3 Then strAmount = Left(strAmount, Len(strAmount) - 3) Else strAmount = "" End If Count = Count + 1 Loop Select Case BigDenom Case "" BigDenom = "Zero " Case "One" BigDenom = "One " Case Else BigDenom = BigDenom & " " End Select ScriptEng = BigDenom End Function Private Function GetHundreds(ByVal MyNumber) Dim result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) If Mid(MyNumber, 1, 1)<>"0" Then result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If If Mid(MyNumber, 1, 1)<>"0" And (Mid(MyNumber, 2, 1)<>"0" Or Mid(MyNumber, 3, 1)<>"0") Then result = result & "And " End If If Mid(MyNumber, 2, 1)<>"0" Then result = result & GetTens(Mid(MyNumber, 2)) Else result = result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = result End Function Private Function GetTens(TensText) Dim result As String result = "" If Val(Left(TensText, 1)) = 1 Then Select Case Val(TensText) Case 10: result = "Ten" Case 11: result = "Eleven" Case 12: result = "Twelve" Case 13: result = "Thirteen" Case 14: result = "Fourteen" Case 15: result = "Fifteen" Case 16: result = "Sixteen" Case 17: result = "Seventeen" Case 18: result = "Eighteen" Case 19: result = "Nineteen" " Case Else End Select Else Select Case Val(Left(TensText, 1)) Case 2: result = "Twenty " Case 3: result = "Thirty " Case 4: result = "Forty " Case 5: result = "Fifty " Case 6: result = "Sixty" Case 7: result = "Seventy" Case 8: result = "Eighty" Case 9: result = "Ninety" Case Else End Select result = result & GetDigit _ (Right(TensText, 1)) End If GetTens = result End Function Private Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function

So, the function was created, to use it, just enter the Propis cell with the necessary arguments, for example, if we need to write the amount in words in rubles and kopecks and in Russian, then the formula will look like this.

Propis(B2;"RUB";"RU";1)

Numbers in words with kopecks in capital or lowercase letters in Excel

Here is the VBA code for the custom function. Display the amount in words with kopecks and select the first capital or lowercase letter

Function RubIn Writing (Amount As Double, Optional Without_kopecks As Boolean = False, _ Optional CopIn Words As Boolean = False, Optional fillinCapital As Boolean = True) As String "Function for writing the amount in words Dim ed, des, sot, ten, razr, dec Dim i As Integer, str As String, s As String Dim intPart As String, frPart As String Dim mlnEnd, tscEnd, razrEnd, rub, cop dec = Array("", "one", "two", "three", "four ", "five", "six", "seven", "eight", "nine") ed = Array("", "one", "two", "three", "four", "five", " six", "seven", "eight", "nine") ten = Array("ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen" , "eighteen", "nineteen") des = Array("", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety" ) sot = Array("", "one hundred", "two hundred", "three hundred", "four hundred", "five hundred", "six hundred", "seven hundred", "eight hundred", "nine hundred") razr = Array("" , "thousand", "million", "billion") mlnEnd = Array("ov", " ", "a", "a", "a", "ov", "ov", "ov", "ov ", "ov ") tscEnd = Array(" ", "a ", "and ", "and ", "and ", " ", " ", " ", " ", " ") razrEnd = Array(mlnEnd, mlnEnd, tscEnd, "") rub = Array("rubles", "ruble", "ruble", "ruble", "ruble", "rubles", "rubles", "rubles", "rubles", "rubles" ) cop = Array("kopecks", "kopecks", "kopecks", "kopecks", "kopecks", "kopecks", "kopecks", "kopecks", "kopecks", "kopecks") If Amount >= 1000000000000 # Or Amount< 0 Then РубПропись = CVErr(xlErrValue): Exit Function "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& If Round(Сумма, 2) >= 1 Then intPart = Left$(Format(Sum, "000000000000.00"), 12) For i = 0 To 3 s = Mid$(intPart, i * 3 + 1, 3) If s<>"000" Then str = str & sot(CInt(Left$(s, 1))) If Mid$(s, 2, 1) = "1" Then str = str & ten(CInt(Right$(s, 1) ))) Else str = str & des(CInt(Mid$(s, 2, 1))) & IIf(i = 2, dec(CInt(Right$(s, 1))), ed(CInt(Right$ (s, 1)))) End If On Error Resume Next str = str & IIf(Mid$(s, 2, 1) = "1", razr(3 - i) & razrEnd(i)(0), _ razr(3 - i) & razrEnd(i)(CInt(Right$(s, 1)))) On Error GoTo 0 End If Next i str = str & IIf(Mid$(s, 2, 1) = "1 ", rub(0), rub(CInt(Right$(s, 1)))) End If RubCapital = str """"""""""""""""" If Without_kopecks = False Then frPart = Right$(Format(Amount, "0.00"), 2) If frPart = "00" Then frPart = "" Else If CopIn Words Then frPart = IIf(Left$(frPart, 1) = "1", ten(CInt( Right$(frPart, 1))) & cop(0), _ des(CInt(Left$(frPart, 1))) & dec(CInt(Right$(frPart, 1))) & cop(CInt(Right$ (frPart, 1)))) Else frPart = IIf(Left$(frPart, 1) = "1", frPart & " " & cop(0), frPart & " " & cop(CInt(Right$(frPart, 1 )))) End If End If RubProps = str & " " & frPart End If """"""""""""""""" " RubProps = str & frPart If fillCapital Then Mid$(RubProps, 1, 1) = UCase(Mid$(RubCapital, 1, 1)) " If fillCapital Then RubCapital = UCase(Left(RubCapital, 1)) & Mid(RubCapital, 2) End Function

• Without kopecks (1), with kopecks (0)
• Kopecks in words (1), in numbers (0)
• Start in words (0), capital letters (1)

This is how the function is used

Note

• This function will work with numbers from 0 to 99,999,999
• Before copying the code, switch the keyboard layout to Russian (for correct copying of Russian text)
• VBA code must be pasted into all files ( Excel Workbooks), where you want it to run
• After inserting the code, you need to save the file with xlsm macro support (in Excel, starting from version 2007)
• The function can either be entered manually, or, if you have forgotten how to write it, through the function wizard (button fx in the formula bar, category User Defined)

If you are in doubt about how to write something in English or Russian, use our translator for numerals. In order to perform a translation, enter the required number in the form of numbers and the program will calculate its written form.

IN currently The translator's limitation is a limit of 18 digits before the decimal point (for integer values) and 18 digits after the decimal point (for translating values ​​containing a fractional part).

Number:

As you can see above, several English spellings are given for the number being translated. With their help, you can tell the difference between American English (AmE) and British English (BrE). For each of them, in turn, several spelling options can also be provided; which one to use in the text depends on the context of the sentence.

This program copes equally easily with converting numbers into text as for in English, and for Russian. Those. If you need to write a number in words in Russian, simply enter it in numeric format and click the "Translate" button.

Designations

When translating into Russian, the translator takes into account possible changes in gender for given number, which are indicated by the following icons:
- feminine, - masculine, - neuter.

In addition to numbers, our program can also write various monetary amounts in words. In this case, the transfer is carried out into three different currencies at once: rubles, dollars and pounds sterling.

The button is used to switch between the “Number in words” and “Amount in words” modes.

First of all, we focus on people learning English. language, but if this translator is useful to someone else (for example, when filling out business documents), we will be very happy.

When displaying various reports, it is often necessary to convert a numeric value into a string in words. In 1C this is solved very simply: the function NumberInWord() is provided for this.

When forming a number in words, it is possible to display next to it the subject of calculation of this number in the required gender and case. Subject of calculus- this is what the converted number measures, for example, money, boxes, carriages, televisions, etc.

• Without the subject of calculus:
  • Twenty two;
  • Three hundred eighty four.
• With the subject of calculus:
  • Thirty-eight doubloons;
  • Twelve ships;
  • Two houses.

Examples

First, let's give examples of conversion to find out the capabilities of 1C:

// ***** Output numbers: ***** NumberIn Words(9876.54); // Nine thousand eight hundred seventy six 54 NumberIn words (1234.56, "L=ru_RU;DP=TRUE" , "whole, whole, whole, f, hundredth, hundredth, hundredth, f, 2"); // One thousand two hundred thirty-four point fifty-six hundredths NumberIn words (7432, "L=ru_RU;NP=False" , ",0" ); // Seven thousand four hundred thirty two // ***** Withdrawal of funds: ***** NumberIn words (9845.11, "Л=ru_RU" , ); // Nine thousand eight hundred forty-five rubles 11 kopecks Number in words (2845.11, "L=ru_RU;DP=True" , "ruble, ruble, ruble, m, kopeck, kopeck, kopeck, w, 2"); // Two thousand eight hundred forty-five rubles eleven kopecks Number in words (555.16, "L=ru_RU;DP=True" , "dollar, dollar, dollars, m, cent, cent, cents, m, 2"); // Five hundred fifty-five dollars sixteen cents Number in words (453.23, "L=ru_RU;DP=True" , "euro, euro, euro, m, cent, cent, cents, m, 2"); // Four hundred fifty-three euros twenty-three cents Number in words (812.44, "L=en_US;DP=True" , "dollar, dollars, cent, cents, 2"); // Eight hundred twelve dollars forty four cents NumberIn Words (3945.76, "L=en_US;DP=True" , ​​"euro, euros, cent, cents, 2" ); // Three thousand nine hundred forty five euros seventy six cents // ***** You can convert not only monetary units: ***** Number in words (535 , "Л=ru_RU" , "box, boxes, boxes, w, w, 0"); // Five hundred thirty-five boxes Number in words (342 , "Л=ru_RU" , "TV, TV, TVs, m, m, 0"); // Three hundred forty-two TVs

Description of working with the function

As we found out, to get a number in words, use the function

NumberIn words (< Число >, < FormatString>, < Item ParametersCalculus>)

The function returns a string value. The following are passed as input parameters:

• Number is the value to be converted;
• FormatString - formatting options:
  • L — Localization code (for example, u_RU — Russian; en_US — English, de_DE — German). By default, the operating system settings are used.
  • NP - Display the name of the subject of calculus, by default - True.
  • ND - Display the name of the decimal parts of the subject of calculus, by default - True.
  • DP - Display the fractional part in words/numbers, default - False.
  • AI - Deduce the conjunction and. Analyzed only for English localization. Possible values: “Not Use”, “Use”.
• Calculus Subject Parameters - comma-separated listing of the parameters of the calculus subject. The format depends on the localization.

For the Russian language, the parameters of the subject of calculus have the form “ruble, ruble, rubles, m, kopeck, kopecks, kopecks, zh, 2”, where:

• ruble – singular nominative case;
• ruble – singular genitive case;
  rubles – plural genitive case;
  m – masculine (w – feminine, s – neuter);
  “kopeck, kopeck, kopeck, w” - fraction, similar to the subject of calculus (may be absent);
  “2” – the number of digits of the fractional part (may be absent, default is 2).

Use in standard solutions

In configurations built on , in a common module WorkWithRatesCurrencies there is a function

FormAmountInWords(AmountNumber, Currency, Withdraw Amount Without Kopecks= False)

The function is used to convert currency amounts into a string in words.

Still have questions?
Ask in the comments to the article.