How to write the amount, number, numbers in words in Excel. How to write the sum, number, numbers in words in Excel

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

Currently, the translator's limit is 18 decimal places (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 translated number. 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 is equally easy to cope with the translation of numbers into text, as for in English, and for Russian. Those. if you need to write the number in words in Russian, just enter it in number format and click the "Translate" button.

Notation

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 also knows how to write various amounts of money in words. In this case, the transfer is carried out immediately in three different currencies: 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.

It is a very common task 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, numbers with text.

As a rule, this is required in trade, accounting and other areas where settlements are made with in cash. Usually it is necessary to transfer 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 the total amount in rubles 1526.23

We need to write this figure in rubles and it is desirable to indicate the penny as well. To do this, we will create a special universal user-defined function that will look like this

Propis(Amount ;Money ;lang ;Prec )

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

lang - this is the language in which you need to withdraw the amount, two languages ​​\u200b\u200bare available English and Russian (“EN”, “RU”) - we also indicate in quotes

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

Thus, you can write the amount in rubles, dollars or euros in words in Russian or English letters, along with the fractional part, while 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 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 function macro for sum 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 = "rubles" w_rus_d = "dollars" w_rus_e = "euro" w_en_r = "rubles" w_en_d = "dollars" w_en_e = "euro" Case Else w_rus_r = "rubles" 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 = "penny" 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 = "penny" 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 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 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 & "thousands" 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 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 Mid(MyNumber, 1, 1)<>"0" And (Mid(MyNumber, 2, 1)<>"0" Or Mid(MyNumber, 3, 1)<>"0") Then result = result & "And " End 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 has been 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 pennies in uppercase or lowercase letters in Excel

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

Function RubWrite(Amount As Double, Optional WithoutCopecks As Boolean = False, _ Optional COP As Boolean = False, Optional Capitalize As Boolean = True) As String "Function to write sum 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("" , "thousands", "million", "billion") mlnEnd = Array("s", " ", "a", "a", "a", "s", "s", "s", "s ", "s") 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("penny", "penny", "penny", "penny", "penny", "penny", "penny", "penny", "penny", "penny") If Sum >= 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 RubProfile = str """"""""""""""""" If No_kopeks = False Then frPart = Right$(Format(Amount, "0.00"), 2) If frPart = "00" Then frPart = "" Else If CopyWritten 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 RubCapital = str & " " & frPart End If """"""""""""""""" RubCapital = str & frPart If startCapital Then Mid$(RubCapital, 1, 1) = UCase(Mid$(RubCapital, 1, 1)) " If startCapital Then RubCapital = UCase(Left(RubCapital, 1)) & Mid(RubCapital, 2) End Function

• Without kopecks (1), with kopecks (0)
• Pennies in words (1), number (0)
• Start in cursive (0), capital (1)

Here is how the function is used

Note

• This function will work with numbers from 0 to 99999999
• Before copying the code, switch the keyboard layout to Russian (for correct copying of Russian text)
• VBA code must be inserted into all files ( Excel Books) where you want it to work
• After pasting the code, you need to save the macro-enabled xlsm file (in Excel since 2007 version)
• The function can either be typed manually, or, if you forgot how it is written, through the function wizard (button fx in the formula bar, User Defined category)

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

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

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

Examples

To begin with, we give examples of conversion to find out the capabilities of 1C:

// ***** Output numbers: ***** Number in words (9876.54 ); // Nine thousand eight hundred and seventy six 54 Number in words (1234.56 , "L=ru_RU;DP=TRUE" , "whole, whole, whole, f, hundredth, hundredth, hundredths, f, 2"); // One thousand two hundred and thirty four point fifty six hundredths Number in words (7432 , "L=ru_RU; NR=False" , ",0" ); // Seven thousand four hundred and thirty two // ***** Cash out: ***** Number in words (9845.11 , "L=ru_RU" , ); // Nine thousand eight hundred forty-five rubles 11 kopecks Number in words (2845.11 , "L=ru_RU;DP=True" , "ruble, ruble, rubles, m, penny, penny, penny, f, 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, cent, 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, cent, m, 2"); // Four hundred and fifty three euros twenty three cents NumberWords (812.44 , "L=en_US;DP=True" , "dollar, dollars, cent, cents, 2"); // Eight hundred and twelve dollars forty four cents NumberWords(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 , "L=ru_RU" , "box, boxes, boxes, f, f, 0"); // Five hundred thirty-five boxes Number in words (342 , "L=ru_RU" , "tv, tv, tv, m, m, 0"); // Three hundred and forty-two TVs

Description of work with the function

As we found out, to get the number in words, the function is used

Number in words (< Число >, < FormatString>, < ParametersItemCalculus>)

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). The operating system settings are used by default.
  • NP - Display the name of the subject of calculus, by default - True.
  • ND - Display the name of the decimal parts of the subject of calculation, by default - True.
  • DP - Display the fractional part in words/number, by default - False.
  • AI - Output union and. Analyzed for English localization only. Possible values: "Don't Use", "Use".
• CalculusItemParameters - comma-separated enumeration of the parameters of the calculus object. The format depends on the localization.

For the Russian language, the parameters of the subject of calculation are of the form "ruble, ruble, rubles, m, kopeck, kopeck, kopeck, f, 2", where:

• ruble - singular nominative;
• ruble - singular genitive;
  rubles - plural genitive;
  m - masculine (g - feminine, s - neuter);
  “penny, penny, penny, w” - a fractional part, similar to the subject of calculation (may be absent);
  "2" - the number of digits of the fractional part (may be absent, the default is 2).

Use in typical solutions

In configurations built on , in the common module WorkCurrency Rates there is a function

GenerateAmountWords(SumNumber , Currency , WithdrawAmountWithoutKopecks= false)

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

Do you have any questions?
Ask in the comments to the article.