D
D
Dmitry Krymtsev2017-08-31 13:51:06
Google Sheets
Dmitry Krymtsev, 2017-08-31 13:51:06

How to make the amount in words in Google Sheets?

Subject. I haven't been able to google for over an hour now. There used to be an add-on, now there is no such thing.
Help out!
Thank you!

Answer the question

In order to leave comments, you need to log in

7 answer(s)
D
Dmitry Krymtsev, 2017-08-31
@krimtsev

Found. You need to open " Tools -> Script Editor " yourself and add the script.
Further, in the place we need, specify the function =number_to_string(cell number) .
I also attach the working code under the spoiler.

spoiler
function number_to_string(_number) {
        var _arr_numbers = new Array();
        _arr_numbers[1] = new Array('', 'один', 'два', 'три', 'четыре', 'пять', 'шесть', 'семь', 'восемь', 'девять', 'десять', 'одиннадцать', 'двенадцать', 'тринадцать', 'четырнадцать', 'пятнадцать', 'шестнадцать', 'семнадцать', 'восемнадцать', 'девятнадцать');
        _arr_numbers[2] = new Array('', '', 'двадцать', 'тридцать', 'сорок', 'пятьдесят', 'шестьдесят', 'семьдесят', 'восемьдесят', 'девяносто');
        _arr_numbers[3] = new Array('', 'сто', 'двести', 'триста', 'четыреста', 'пятьсот', 'шестьсот', 'семьсот', 'восемьсот', 'девятьсот');
        function number_parser(_num, _desc) {
                var _string = '';
                var _num_hundred = '';
                if (_num.length == 3) {
                        _num_hundred = _num.substr(0, 1);
                        _num = _num.substr(1, 3);
                        _string = _arr_numbers[3][_num_hundred] + ' ';
                }
                if (_num < 20) _string += _arr_numbers[1][parseFloat(_num)] + ' ';
                else {
                        var _first_num = _num.substr(0, 1);
                        var _second_num = _num.substr(1, 2);
                        _string += _arr_numbers[2][_first_num] + ' ' + _arr_numbers[1][_second_num] + ' ';
                }              
                switch (_desc){
                        case 0:
                                var _last_num = parseFloat(_num.substr(-1));
                                if (_last_num == 1) _string += 'рубль';
                                else if (_last_num > 1 && _last_num < 5) _string += 'рубля';
                                else _string += 'рублей';
                                break;
                        case 1:
                                var _last_num = parseFloat(_num.substr(-1));
                                if (_last_num == 1) _string += 'тысяча ';
                                else if (_last_num > 1 && _last_num < 5) _string += 'тысячи ';
                                else _string += 'тысяч ';
                                _string = _string.replace('один ', 'одна ');
                                _string = _string.replace('два ', 'две ');
                                break;
                        case 2:
                                var _last_num = parseFloat(_num.substr(-1));
                                if (_last_num == 1) _string += 'миллион ';
                                else if (_last_num > 1 && _last_num < 5) _string += 'миллиона ';
                                else _string += 'миллионов ';
                                break;
                        case 3:
                                var _last_num = parseFloat(_num.substr(-1));
                                if (_last_num == 1) _string += 'миллиард ';
                                else if (_last_num > 1 && _last_num < 5) _string += 'миллиарда ';
                                else _string += 'миллиардов ';
                                break;
                }
                _string = _string.replace('  ', ' ');
                return _string;
        }
        function decimals_parser(_num) {
                var _first_num = _num.substr(0, 1);
                var _second_num = parseFloat(_num.substr(1, 2));
                var _string = ' ' + _first_num + _second_num;
                if (_second_num == 1) _string += ' копейка';
                else if (_second_num > 1 && _second_num < 5) _string += ' копейки';
                else _string += ' копеек';
                return _string;
        }
        if (!_number || _number == 0) return false;
        if (typeof _number !== 'number') {
                _number = _number.replace(',', '.');
                _number = parseFloat(_number);
                if (isNaN(_number)) return false;
        }
        _number = _number.toFixed(2);
        if(_number.indexOf('.') != -1) {
                var _number_arr = _number.split('.');
                var _number = _number_arr[0];
                var _number_decimals = _number_arr[1];
        }
        var _number_length = _number.length;
        var _string = '';
        var _num_parser = '';
        var _count = 0;
        for (var _p = (_number_length - 1); _p >= 0; _p--) {
                var _num_digit = _number.substr(_p, 1);
                _num_parser = _num_digit +  _num_parser;
                if ((_num_parser.length == 3 || _p == 0) && !isNaN(parseFloat(_num_parser))) {
                        _string = number_parser(_num_parser, _count) + _string;
                        _num_parser = '';
                        _count++;
                }
        }
        if (_number_decimals) _string += decimals_parser(_number_decimals);
        return _string;
}

E
Eugene, 2018-12-11
@Sif_on


To convert numbers/numbers/amounts to text/letters/words in google spreadsheets ,
you can use the code below:
Обязательно выберите валюту. Просто скопируйте нужный код валюты в конец основного (приведенного выше).
Рубли:
Гривны:
Для перевода на украинский язык используйте штатный переводчик:
Введите цифру в ячейку A1
и наслаждайтесь результатом.

V
Vadim7403, 2020-04-22
@Vadim7403

Here is the script for the Ukrainian language (Ukrainian language)
// open the Script Editor Tool, paste it, save it to your disk
--------------------------------- -----
var mapNumbers = {
0 : [2, 1, "null"],
1 : [0, 2, "one", "one"],
2 : [1, 2, "two", "two "],
3 : [1, 1, "three"],
4 : [1, 1, "chotiri"],
5 : [2, 1, "five"],
6 : [2, 1, "six "],
7 : [2, 1, "sіm"],
8 : [2, 1, "vіsіm"],
9 : [2, 1, "nine"],
10 : [2, 1, "ten "],
11 : [2, 1, "eleven"],
12 : [2, 1, "twelve"],
13 :[2, 1, "thirteen"],
14 : [2, 1, "fifteen"],
15 : [2, 1, "fifteen"],
], 700 : [2, 1, "simsot"], 800 : [2, 1, "simsot"], 900 : [2, 1, "nine hundred"] }; varmapOrders =
[{ _Gender : true, _arrStates : ["hryvnia", "hryvnia", "hryvnia"] },
{ _Gender : false, _arrStates : ["thousand", "thousand", "thousand"] },
{ _Gender : true, _arrStates : ["million", "million", "million"] },
{ _Gender : true, _arrStates : ["billion", "billion", "billion"]},
{ _Gender : true, _arrStates : ["trillion" , "trillion", "trillion"] } ];
var objKop = { _Gender : false, _arrStates : ["copy", "copy", "copy"] };
function Value(dVal, bGender) {
var xVal = mapNumbers[dVal];
if (xVal[1] == 1) {
return xVal[2];
} else {
return xVal[2 + (bGender ? 0 : 1)];
}
}
function From0To999(fValue, oObjDesc, fnAddNum, fnAddDesc)
{ var nCurrState = 2;
if (Math.floor(fValue/100) > 0) {
var fCurr = Math.floor(fValue/100)*100;
fnAddNum(Value(fCurr, oObjDesc._Gender));
nCurrState = mapNumbers[fCurr][0];
fValue -= fCurr;
}
if (fValue < 20) {
if (Math.floor(fValue) > 0) {
fnAddNum(Value(fValue, oObjDesc._Gender));
nCurrState = mapNumbers[fValue][0];
}
} else {
var fCurr = Math.floor(fValue/10)*10;
fnAddNum(Value(fCurr, oObjDesc._Gender));
nCurrState = mapNumbers[fCurr][0];
fValue -= fCurr;
if (Math.floor(fValue) > 0) {
fnAddNum(Value(fValue, oObjDesc._Gender));
nCurrState = mapNumbers[fValue][0];
}
}
fnAddDesc(oObjDesc._arrStates[nCurrState]);
}
function FloatToSamplesInWordsRus(fAmount)
{
var fInt = Math.floor(fAmount + 0.005);
var fDec = Math.floor(((fAmount - fInt) * 100) + 0.5);
var arrRet = [];
var iOrder = 0;
var arrThousands = [];
for (; fInt > 0.9999; fInt/=1000) {
arrThousands.push(Math.floor(fInt % 1000));
}
if (arrThousands.length == 0) {
arrThousands.push(0);
}
function PushToRes(strVal) {
arrRet.push(strVal); }
for (var iSouth = arrThousands.length-1; iSouth >= 0; --iSouth) {
if (arrThousands[iSouth] == 0) {
continue;
}
From0To999(arrThousands[iSouth], mapOrders[iSouth], PushToRes, PushToRes); }
if (arrThousands[0] == 0) {
// Handle zero amount
if (arrThousands.length == 1) {
PushToRes(Value(0, mapOrders[0]._Gender)); }
var nCurrState = 2;
PushToRes(mapOrders[0]._arrStates[nCurrState]);
}
if (arrRet.length > 0) {
// Capitalize first letter
arrRet[0] = arrRet[0].match(/^(.)/)[1].toLocaleUpperCase() + arrRet[0].match(/ ^.(.*)$/)[1]; }
arrRet.push((fDec < 10) ? ("0" + fDec) : ("" + fDec));
From0To999(fDec, objKop, function() {}, PushToRes);
return arrRet. join(" ");
}

A
aln2187, 2018-02-16
@aln2187

Tell me how to change the code so that the second part of the "penny" is also text?

A
Alexander Ivanov, 2019-10-08
@oshliaer

You can use addon for Tables NUMBERTEXT
5d9c015768e80721417047.png
Number in words in current locale
=NUMBERTEXT(1) // one
Number in words in Russian locale
=NUMBERTEXT(1;"ru") // один
Number in words in Ukrainian locale
=NUMBERTEXT(1;"uk") // один
Number in given currency in words in current locale
=MONEYTEXT(1; "USD"; "uk") // Один долар США

3
3dinukraine, 2022-02-15
@3dinukraine

for hryvnia Ukrainian
clitinka H18
fourteenz"\"n'fifteenz"\"sixteenz"\"sixteenz"\"nine'fifteenz"\"nine'fifteenz"};{"";1;"twentyz";"thirtyz";"fortyz";"n'yatzenz" ";"shіsttenz";"sіmdesyz";"vіsіmіdіzz";"nineteenthz"}&{""\"onez"\"twoz"\"threez"\"chotiriz"\"p'yatz"\"shіstz" \"sіmz"\"vіsіmz"\"nine"});MID(TEXT(H18;"000000000000.00");11;1)+1;MID(TEXT(H18;"000000000000.00");12;1) +1));"z";" ")&IF(TRUNC(TEXT(H18;"000000000000.00"));"";"Null ")&"mane"&VLOOKUP(MOD(MAX(MOD(MID(TEXT(H18;"000000000000.00");11;

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question