To auto convert numbers into words in Indian Rupees within Google Sheets, you can use a custom formula called SpellNumberINR. Unlike standard formulas in Google Sheets, this function is not built-in. Instead, first you’ll need to create it using Google Apps Script. Once implemented, the formula will efficiently convert numeric values into their corresponding word format in INR.
How to Auto Convert Numbers into Words in Indian Rupees?
First you need to create the formula and you will be able to use it. A Step-by-Step Guide to Create and Use the SpellNumberINR Function:
- Open Google Sheets
- Go to the Menu Bar at the top of the page.
- Select the Extensions Tab.
- Open Apps Script: from the Extensions dropdown.
- Go to Code.gs, Apps Script editor. If there is no file named Code.gs, you can create a new one by clicking on the “+” icon or if already, just open it.
- Copy the Code provided below, the SpellNumberINR function script.
- Delete any existing Code before typing the SpellNumberINR script in the script editor.
- Paste the copied SpellNumberINR function Code into the script editor or type manually.
- Save project to Drive
- Run the selected function
- Check the execution log below
- Showing results as
- Execution started
- Execution completed
- Showing results as
- Go to back in Sheets and type function with equal sign eg.
=SpellNumberINR(A1)
After following these steps, you can now use the SpellNumberINR function directly in your Google Sheets to convert numbers to words in INR.
Apps Scripts code: SpellNumberINR
/**
msofficegeek.com Spell Number Indian Rupees
@customfunction
*/
function SpellNumberINR(input) {
const rupees = Number(parseInt(input, 10));
const output = [];
if (rupees === 0) {
output.push("zero");
} else if (rupees === 1) {
output.push("one");
} else {
const crores = Math.floor(rupees / 10000000) % 100;
if (crores > 0) {
output.push(${getHundreds(crores)} crore);
}
const lakhs = Math.floor(rupees / 100000) % 100;
if (lakhs > 0) {
output.push(${getHundreds(lakhs)} lakh);
}
const thousands = Math.floor(rupees / 1000) % 100;
if (thousands > 0) {
output.push(${getHundreds(thousands)} thousand);
}
const hundreds = Math.floor((rupees % 1000) / 100);
if (hundreds > 0 && hundreds < 10) {
output.push(${getOnes(hundreds)} hundred);
}
const tens = rupees % 100;
if (tens > 0) {
if (rupees > 100) output.push();
output.push(${getHundreds(tens)});
}
}
return ["", output, "only"]
.join(" ")
.split(/\s/)
.filter((e) => e)
.map((e) => e.substr(0, 1).toUpperCase() + e.substr(1))
.join(" ");
}
function getOnes(number) {
const ones = ["", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine"];
return ones[number] || "";
}
function getTeens(number) {
const teens = ["ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen"];
return teens[number] || "";
}
function getTens(number) {
const tens = ["", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"];
return tens[number] || "";
}
function getHundreds(num) {
if (num > 0 && num < 10) { return getOnes(num); } if (num >= 10 && num < 20) { return getTeens(num % 10); } if (num >= 20 && num < 100) {
return ${getTens(Math.floor(num / 10))} ${getOnes(num % 10)};
}
return "";
}The provided code defines a custom function in JavaScript for Google Sheets or any similar environment where custom functions can be used, like Google Apps Script. The function, named SpellNumberINR, converts a numeric value into its equivalent representation in Indian Rupees spelled out in words as (crores, lakhs, thousands, etc).
The above function has some limitations and cannot address the following issues effectively:
- The function does not handle fractional amounts (e.g., paise in INR)
- It lacks the ability to handle negative numbers
Here’s the revised version of the function:
Here’s the updated and improved version of the SpellNumberINR function that addresses the issues with handling negative numbers, fractional amounts (paise), and repeated calculations:
/**
* Convert numbers into words in Indian Rupees
* @param {number} input - The number to convert
* @return {string} The number in words with Indian currency format
* @customfunction
*/
function SpellNumberINR(input) {
if (isNaN(input) || input === null || input === undefined) {
return "Invalid input";
}
// Handle negative numbers
const isNegative = input < 0;
const absoluteValue = Math.abs(input);
// Separate rupees and paise
const rupees = Math.floor(absoluteValue);
const paise = Math.round((absoluteValue - rupees) * 100);
// Handle zero case
if (rupees === 0 && paise === 0) {
return "Zero Rupees Only";
}
const output = [];
// Convert rupees part
if (rupees > 0) {
output.push(`${convertToWords(rupees)} Rupee${rupees > 1 ? "s" : ""}`);
}
// Convert paise part
if (paise > 0) {
output.push(`${convertToWords(paise)} Paise`);
}
// Combine results
let result = output.join(" and ");
// Handle negative output
if (isNegative) {
result = "Negative " + result;
}
return result + " Only";
}
/**
* Helper function to convert numbers to words based on the Indian numbering system
* @param {number} number - The number to convert
* @return {string} The number in words
*/
function convertToWords(number) {
if (number === 0) return "";
const ones = ["", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"];
const teens = ["Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"];
const tens = ["", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"];
// Indian number system scales
const scales = ["", "Thousand", "Lakh", "Crore"];
// Handle numbers greater than 999 crores
if (number >= 1000000000000) {
return "Number too large";
}
let words = [];
// Handle Crores
const crores = Math.floor(number / 10000000);
if (crores > 0) {
words.push(`${convertToWords(crores)} Crore`);
number %= 10000000;
}
// Handle Lakhs
const lakhs = Math.floor(number / 100000);
if (lakhs > 0) {
words.push(`${convertToWords(lakhs)} Lakh`);
number %= 100000;
}
// Handle Thousands
const thousands = Math.floor(number / 1000);
if (thousands > 0) {
words.push(`${convertToWords(thousands)} Thousand`);
number %= 1000;
}
// Handle Hundreds
const hundreds = Math.floor(number / 100);
if (hundreds > 0) {
words.push(`${ones[hundreds]} Hundred`);
number %= 100;
}
// Handle remaining two digits
if (number > 0) {
if (words.length > 0) {
words.push("and");
}
if (number < 10) {
words.push(ones[number]);
} else if (number < 20) {
words.push(teens[number - 10]);
} else {
const tensDigit = Math.floor(number / 10);
const onesDigit = number % 10;
if (onesDigit === 0) {
words.push(tens[tensDigit]);
} else {
words.push(`${tens[tensDigit]} ${ones[onesDigit]}`);
}
}
}
return words.join(" ");
}This function auto converts numerical values into Indian currency words, handling rupees and paise(specially large numbers e.g..>999crores). It processes negative numbers, zero values, and follows the Indian numbering system (crores, lakhs, thousands). The function includes proper currency notation and “Only” suffix for formal representation.
The concept of converting numbers to words has been implemented by countless individuals and organizations over time. The examples provided for Google Sheets (using Apps Script) are commonly shared open-source adaptations available on forums like Stack Overflow, GitHub, and community blogs.
Here we customize the SpellNumberINR function to meet the specific requirements of the Indian currency format. This customization ensures that numbers are accurately converted into words following the INR currency structure, including denominations like Rupees and Paise. It provides a tailored solution for converting numerical values into words, aligning with the Indian financial and accounting standards.
If you have any questions, need assistance, or encounter challenges, feel free to share your concerns in the comments. The CountLen team is dedicated to providing quick and effective solutions. If you notice any inaccuracies or misleading information, please provide feedback—we’re here to support you!
