Why / For What?
To change numbers to words. I often have to convert the marks to words and sometimes the money value to words.
To convert the money value to words I use the add-on (available at Google Workspace marketplace) called NumberText. I wanted a similar feature but that doesn’t have to convert to number values considering digit position. I just wanted to convert any number to words just to reduce confusion when it is published or documented.
Example: 101 -> One Hundred and One (or) One Hundred and One dollar (or) One Zero One
We can do it manually if it is not for hundreds of numbers. So I made a simple script for Google Spreadsheet. This script creates a custom formula that can be used inside the sheet anywhere.
Steps
Step 1: First, create a spreadsheet inside Google Drive.
Step 2: Refer to the above pic to open Apps Script manager for the current sheet.
Step 3: In the main code section (code.gs) copy the below code, save it and run it once. Ignore the warnings and errors. (Refer to the below screenshot).
function Spellnumber(input) {
var number = input;
var output = '', currentNumb;
var sNumber = number.toString();
var word = ['zero ', 'one ', 'two ', 'three ', 'four ', 'five ', 'six ', 'seven ', 'eight ', 'nine ']
for (var i = 0, len = sNumber.length; i < len; i += 1) {
currentNumb = sNumber.charAt(i);
for (var n = 0; n <=9; n+=1)
{
if (currentNumb == n) {
output = output + word[n];
}
}
}
return output;
}
Step 4: Now close the Apps Scripts window. Go back to the Spreadsheet.
Accessing Custom Formula
You can now use the custom function (formula) we just created using Apps Script as you use built-in functions like =sum(A1, B1)
To convert a number to words, use
=spellnumber(55)This will give the output, “five five”.
To convert a value of a cell “A1” value to words, refer cell address in the formula as follows.
=spellnumber(A1)Refer to the screenshot for the usage and sample output.
If you have any doubts or queries, just comment below. If this is help full, consider buying me a coffee.