Amount, number, figures in words

Function sum in words in Russian

After installing the VBA-Excel add-in, functionality will be added to insert amounts in words. It contains a convenient input form (see the figure on the right), as well as the function SUMMAPROPUT , which can be used in the same way as any built-in Excel.
You will be able to indicate the number in words in any case , and also select the output format that is necessary in a particular case.

You can indicate the amount in words in the contract in rubles (with or without kopecks), in US dollars or euros (with or without cents). In addition to the contract amount, you can also write down the term in calendar or working days . You can indicate the quantity of goods in words in pieces .

The functionality is quite flexible and allows you to customize the display of brackets, the format of the fractional part, indicate the amount with a capital or small letter, and much more.

Inserting an amount in words via a custom form

The easiest way to enter the amount in words is to fill out a special form.

Select the cell in which you want to insert the number in words.
On the VBA-Excel , click the Amount in words and select the language Russian .
A dialog box appears for selecting function parameters. Fill it out as needed. The settings are automatically saved so you don't have to repeat the steps next time.
Click Insert as text , then the amount in words will be inserted into the cell as text. Click Insert as a formula if a formula must be inserted into the cell; in this case, the amount in words will automatically change when editing the number.

The second way to insert a sum in words is to enter a formula in a cell: =SUMA IN WRITTEN(A1) , where A1 is a reference to the cell with the formula.

Next, let's take a closer look at the function syntax.

Detailed Function Syntax

To use the function, it is not necessary to constantly call the form with parameters. The function can be used in the same way as other Excel functions. The function has the following variables:

=SUMMAPROP(Number; [Case]; [Data_type]; [Fraction_in words]; [Duplicate_number]; [Parentheses]; [Capital])

Square brackets mean that the variable is optional, in which case the default value is used.

Number
- a reference to a cell or number (not necessarily in numeric format) that must be written in words.
[Case] -
a number from 1 to 6 that determines the case

Nominative (default if parameter is not specified)
Genitive
Dative
Accusative
Instrumental
Prepositional

[Data_type] -
a number from 0 to 8 to be added after the amount in words in the required case data type

Nothing (default if parameter is not specified)
Rubles
U.S. dollars
Euro
Calendar days
Work days
Days
Pieces
Integer + fractional part

[Fraction_in words] -
value 0 or 1, which indicates the need to write the fractional part of the number in words

Do not print the fractional part of a number in words (default)
Indicate in words also the fractional part of the number

[Duplicate_number] -
value 0 or 1, which indicates the need to duplicate the number before the amount in words

Hide the number before the amount in words
Duplicate the numeric value of the amount in words (default)

[Brackets] -
value 0 or 1, which indicates the need to write the amount in words in brackets

Remove parentheses from sum in words
Place the amount in words in parentheses (default)

[Capital] -
value 0 or 1, which indicates the need to capitalize the first letter of the amount in words

All letters of the amount in words are lowercase
Make first letter capital (default)

Let's look at the syntax of the function using examples:

Declension of numerals by cases.

Adding a data type after specifying the amount in words. Please note that if the second variable (case) is not specified, it is specified by default in the nominative case.

How to write the fractional part of a number using a function.

Various formats for displaying amounts in words.

PropsAmount

MulTEx » May 1, 2011 Dmitry 68318 views

This function is part of the MulTEx add-on

Description, installation, removal and update
Complete list of MulTEx commands and functions
Frequently asked questions about MulTEx

This function is widely in demand by workers in the economic and accounting fields, and, probably, by everyone who is in one way or another involved in accounting for funds and compiling all kinds of forms in Excel using forms. This function displays the specified number in words. Those. from the number 123 one hundred twenty-three will be obtained . It is possible to display rubles and kopecks (one hundred and twenty-three rubles and twelve kopecks), whole and hundredths (one hundred and twenty-three point twelve) and any arbitrary units of measurement, as well as specify the text output language and the number of decimal places for translation into words. At the moment, three languages are available in default units: Russian, English and Ukrainian.

Calling a command through a standard dialog: Function Wizard - Category "MulTEx" - CopySum Call from the MulTEx panel: Sum/Search/Functions - Text - CopySum Syntax: = CopySum(I9) = CopySum(I9;2;2;"RUS") = CopySum(I9;2;1;"RUS";"whole,whole,whole,hundredth,hundredth,hundredth"; TRUE) =CopySum(I9,2,1;"RUS";"whole,whole,whole,hundredth, hundredths, hundredths"; 1; 3) = PropsAmount(I9;2;1;"UA";"whole, whole, whole, hundredth, hundredths, hundredths"; 1; 3) = PropsAmount(I9;2;1;" UKR";"whole,whole,whole,hundredth,hundredth,hundredth"; 1; 3) =PropisSum(I9,2,1;"RUS";"whole,whole,whole,hundredth,hundredth,hundredth"; 1; 3; TRUE)

Number (I9) - indicates a number or a link to a cell with a number that needs to be converted. Instead of a reference to cell (I9), you can directly specify the number =WritingSum(145,1,1)

Rubles (2) is an optional argument. Allows you to print the whole part of a number in words. If the value is set to 1, the signature Rubles is added after the integer part of the number. The number itself, denoting rubles, is not converted (1 ruble, 7 rubles). If the value is assigned to 2, the signature Rubles is added after the integer part of the number. The number itself, denoting rubles, is converted into a text representation (One ruble, Seven rubles). If not specified or assigned the value 0, only the number (One) is written, without specifying the units (rubles).

Kopeks (1) is an optional argument. Allows you to display the fractional part of a number in words. If specified or assigned the value 1, the signature Kopecks is added after the fractional part of the number. The number itself, denoting kopecks, is not converted (Two rubles 1 kopeck, Two rubles 4 kopecks). If specified or assigned the value 2, the signature Kopecks is added after the fractional part of the number. The number itself, denoting kopecks, is converted into a text representation of this number (Two rubles one kopeck, Two rubles four kopecks). If the Kopeyki is not specified, or it is assigned the value 0 or FALSE, then only the integer number will be displayed, without taking into account the decimal places (Two). Those. In this case, kopecks are not included in the register. The Rubles and Kopeks arguments can be changed independently of each other. For example, if for the number 23.78 you specify Rubles - 1, and Kopeks - 0, then the following will be written down: 23 rubles. If you specify Rubles - 2, and Kopecks - 1, then the following will be written down: twenty-three rubles 78 kopecks

Language (“RUS”) is an optional argument. Defines the language for displaying numbers. Not case sensitive. Language options available:

“RUS” or not specified: the text will be displayed in Russian. 23.78 will be written as “twenty-three rubles seventy-eight kopecks”
"EN"("en","En"): the text will be displayed in English. 23.78 would be written as “Twenty three dollars seventy eight cents”
“UKR” or “UA”: the text is displayed in Ukrainian: 23.78 will be written as “twenty-three hryvnias and seventy total copies.”

It should be taken into account that if the Units is specified as a combination of its own units, then the language in which the units are written may differ from that specified in the Language parameter. For example, if for the number 23.78 you specify the Units “whole, whole, whole, hundredth, hundredths, hundredths” and set the language to “en”, then the result will be incorrect: Twenty three point seventy eight hundredths. To correctly display whole and hundredths in English, the Units argument must be written as "point,points,points,hundredths,hundredths,hundredths".

Units (“whole, whole, whole, hundredth, hundredths, hundredths”) is an optional argument. This argument is responsible for indicating the units of measurement of numerical indicators. In the argument, you must specify the units of measurement for the number, separated by commas (no spaces!). How it works using the example of “ruble, ruble, rubles, kopek, kopecks, kopecks”: the first three words (ruble, ruble, rubles) are responsible for designating units for the integer part of the number:

ruble - indicates the declination of the unit for the number 1
ruble - the declination of one is indicated for the numbers 2,3,4
rubles - the declination of one is indicated for the number 5 and above

the last three words (kopeck, kopeck, kopeck) are responsible for designating units for the fractional part of a number:

kopeck - indicates the declination of the unit for the number 1
kopecks - the declination of one is indicated for the numbers 2,3,4
kopecks - the declination of one is indicated for the number 5 and above

By default (if the Units is not specified), “ruble, ruble, rubles, kopeck, kopeck, kopeck” is used for the Russian language (“RUS”) and “dollar, dollars, dollars, cent, cents, cents” for the English language (“ EN"). If you specify “whole, whole, whole, hundredth, hundredths, hundredths,” then for the number 23.78 the following will be written out in words:

Twenty-three point seventy-eight hundredths - when specifying the arguments Rubles and Kopeks equal to 2
23 point 78 hundredths - when specifying the arguments Ruble and Kopek equal to 1

If Units - “”, then the units will simply not be written and for the number 24.78 it will be output: twenty-four seventy-eight.

This way you can specify almost any currency and any units of measurement: from wagons to milliliters. This makes the CopySum very flexible and more universal, applicable in almost any document where it is necessary to display certain data in words. In this case, units can be written in any language.

PropStart (TRUE) - Optional argument. Specify the Boolean value TRUE or FALSE, or an expression that returns a Boolean value. If TRUE or 1 is specified, then the first letter of the resulting text will be converted to capital: Two rubles one kopeck. If not specified, FALSE or 0 is specified, then all text will be written in lowercase letters: two rubles one kopeck.

Number of Characters (3) - Optional argument. Specify an integer indicating the number of decimal places that will be displayed in words. By default, if not specified, it takes the value 2. Required for displaying units measured in numbers with three or more decimal places, for example, tons and kilograms, where kilograms must always be denoted by exactly three decimal places: 3.472. After the conversion, the result will be “three tons, four hundred and seventy-two kilograms.” If a number contains more decimal places than specified by NumberCharacters, that number will be rounded to the specified number of decimal places. Up to 5 decimal places are allowed. If a number greater than 5 is specified, the function will still only take into account 5 digits.

INBrackets (TRUE) - Optional argument. Specify the Boolean value TRUE or FALSE, or an expression that returns a Boolean value. If TRUE or 1 is specified, then the result will first display the number itself, and then its words in parentheses: 2,302 (two thousand three hundred two) rubles 01 kopeck. If not specified, FALSE or 0 is specified, then the text is displayed in accordance with the other parameters. If the INBrackets is specified as 1 or TRUE, and the Rubles is specified as 0, then it will be ignored and the text in brackets will still be written in words.

Several practical examples of specifying arguments for displaying words:

Date: Number = 1.07 Rubles - 1 Kopecks - 1 Units - “year, year, years, month, months, months” Result - 1 year 07 months If for Units you specify “month, months, months, day, days, days " - then we get - 1 month 07 days
Units of measurement: Number = 1,07 Rubles — 1 Kopeks — 1 Units - “box, box, boxes, box, boxes, boxes” PropStart - 0 (or FALSE) NumberSigns — not specified Result — 1 box 07 boxes
Number = 68,39 Rubles — 2 Kopeks — 2 Units - “whole, whole, whole, hundredth, hundredth, hundredth” PropStart - 0 (or FALSE) NumberSigns — not specified Result — sixty-eight point thirty-nine
Number = 68,396 Rubles - 2 Kopecks - 2 Language - “RUS” Units - “whole, whole, whole, thousandth, thousandth, thousandth” PropStart - 1 (or TRUE) Number of Signs - 3 Result - Sixty-eight point three hundred ninety-six thousandths
Number = 68.39 Rubles - 2 Kopecks - 2 Language - “EN” Units - “point,points,points,hundredths,hundredths,hundredths” PropStart - 1 (or TRUE) Number of Signs - 2 Result - Sixty eight points thirty nine hundredths
Currency: Number = 5,44 Rubles — 2 Kopeks — 2 Language - not specified (or “ru”) Units - not specified PropStart — 0 (FALSE) or not specified NumberSigns — not specified Result — five rubles forty-four kopecks
Number = 25,44 Rubles — 2 Kopeks — 2 Language - "En" Units - not specified PropStart - not specified NumberSigns — not specified Result — twenty five dollars and forty four cents
Number = 25.44 Rubles - 2 Kopecks - 2 Language - “UA” Units - not specified PropStart - not specified Number of Signs - not specified Result - twenty-five hryvnias forty four kopecks
Number = 2302 Rubles - 0 Kopecks - 0 Language - "RUS" Units - "" PropStart - 1 Number of Signs - 2 Result - 2302 (two thousand three hundred two)
Read also: How to resolve the Connectionfailure error in Yandex browser
Distances: Number = 14021.22 Rubles - 2 Kopecks - 2 Units - “mile, miles, miles, yard, yard, yards” PropStart - not specified Number of Signs - not specified Result - fourteen thousand twenty-one miles twenty-two yards
Weight: Number = 3.472 Rubles - 2 Kopecks - 2 Language - “rus” Units - “ton, tons, tons, kilogram, kilogram, kilogram” PropStart - 1 Number of Signs - 3 Result - Three tons four hundred seventy-two kilograms

Note: This function handles numeric values with a maximum value of Hundreds of Trillions . Numbers greater than 999,999,999,999,999.99999 will not be converted correctly because Excel itself cannot work correctly with numbers exceeding hundreds of trillions (the last digits will be replaced by zeros).

Function sum in words in Ukrainian

Similar functionality for inserting amounts in words is also available for the Ukrainian language. There is also a similar form for easy insertion and the function SUMMAPROPISSUUKR.

To call up the form for inserting the amount in words in Ukrainian, follow these steps:

Select the cell in which you want to insert the number in words.
On the VBA-Excel , click the Amount in words and select the language Ukrainian .
Further actions are similar

Sum function in words in English

The sum function in English is somewhat simpler than those described above. There are no cases and the writing format is always the same.

To insert a number in words in English you need:

Select the cell in which you want to paste the result
On the VBA-Excel , click the Amount in Words and select the language English .
Enter a number and click one of the Insert as formula or Insert as text .

Instead of the input form, you can also use the function =SUMAPREPEN(NUMBER) . The function has one argument: NUMBER - a value or reference to a cell with a number that needs to be converted into text in words.

How to write a sum in Excel in words

To get this feature, you can download the Amount in Words Excel add-in for free on our website.

Place it in any convenient directory and connect. To do this, you must go through the following steps:

Launch Excel and click File.
Click on options.
Next, go to add-ons.
And click on the “Go” button.
To upload the downloaded file, click “Browse”.
And look for it in the folder where you saved it. You will be prompted to copy the file to the add-ons folder, we recommend agreeing. This way Excel will always see this add-in and you can easily find the function for the sum in words.
The required Excel library is connected. A check mark means it can be used. Click OK.
The necessary actions are implemented in the form of the function SUM_IN CAPTION. To apply it, enter a number in cell A1 and position the pointer next to B1. Click the Fx icon next to the formula bar.
And in the complete alphabetical list, find the function SUM_IN CAPITAL. We recommend that you start typing the title. In this case, Excel itself will rewind closer to the desired section.
Click OK and enter the address A1 in the wizard window. You can simply click on the cell with the mouse.
After clicking OK you will see the amount in words.

This "sum_in words" function works in Excel 2007, 2010, 2016

This add-on only works for rubles. If you need another currency, download the file sumprop.xla. By analogy, connect the add-in to get the amount in words in Excel.

To check, enter an arbitrary number in cell A3. Let it be a decimal fraction. We will also call the function insertion window and find new functions for displaying the amount in words.

As you can see, we now have access to:

Suma in cuirsive
Amount in Words Dollars
Amount in Euros
Amount in Euros

Let's convert the number to euros. Select the appropriate function and click on the cell with the number.

After clicking OK we get the result.

With the help of these tools, converting number to written text in Excel is very easy. If for some reason you do not want to install them in Excel, you can use another method.