SpellNumber Indian Rupees Function is a custom Google Sheets function manually created with a Google Apps Script that changes a given number to written words. Thus, it converts a numeric value in Indian Rupees to an amount in words.
Similar to Excel, Google Sheets also doesn’t have an inbuilt function for converting numbers to words. Thus, a separate code was created in Google Apps Script.
The Indian currency system uses lakhs and crores. The lakh determines means one hundred thousand whereas crore represents ten million and hundred crores represents a billion.
In simple terms, the INR Function in Google Sheets will convert Rs.123456789 to Rupees Twelve Crore Thirty-Four Lakh Fifty-Six Thousand Seven Hundred and Eighty-Nine Only.
In this article, we will show you the step-by-step process to create the SpellNumber Function for Indian Currency in Google Sheets.
Key Takeaways
- Google Sheets does not have a built-in number-to-words function, unlike Excel add-ins. The function supports the Indian numbering system (lakh, crore) and converts full values into readable currency text.
- It is created by adding a Google Apps Script via Extensions → Apps Script, making it available in that specific sheet. Once installed, it can instantly convert numbers like 123456789 into full written Indian Rupees format in words.
6 Easy Steps To Create the SpellNumber Indian Rupees Function In Google Sheets
1. Open the Google Sheet in which you want to insert this function. We are opening a new file.
2. Click on the “Extensions” menu in the ribbon.
3. Open “Apps Script”. The following screen will appear:
4. In the Code gs box, select all using Ctrl + A and delete everything.
5. Copy the following code into Code. gs
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * 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("and"); | |
| output.push(`${getHundreds(tens)}`); | |
| } | |
| } | |
| return ["Rupees", …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 ""; | |
| } |
6. Click on the “Save” button or go to the “File” menu and click on the “Save” option.
Your function is ready to use in this Google Sheet.
Important Note: This function of Google Sheets will only be available in the spreadsheet in which you have this script. Yes, it will work if you create a copy of the spreadsheet using the “Make a copy” option.
How To Use the SpellNumberINR Function In Google Sheets?
Let’s try using this function that we inserted in our sheet.
Go to your spreadsheet in which you inserted the function. Write the following numbers in Column A: 123, 12345, 123456, 1234567, 12345678, and 123456789.
In Column B, insert the following formula: =SpellNumberINR(A1)
Use the fill handle to copy the same formula down the column. The following results will be displayed:
Frequently Asked Questions
What does SpellNumber Indian Rupees function do?
It converts numeric values into Indian currency words like “Rupees Twelve Crore…” format.
Is SpellNumber available by default in Google Sheets?
No, it must be created manually using Google Apps Script.
Where does the SpellNumber function work after setup?
It only works in the spreadsheet where the script is added, or its copied version.
Can I use this function for different sheets?
Yes, but you must copy the file using “Make a copy” or re-add the script.
Why is Apps Script needed for this function?
Because Google Sheets does not have a native function for converting numbers to words.
Can this function handle large numbers like crores and billions?
Yes, it can accurately convert large financial values up to billion-scale numbers.
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.
Other SpellNumber Functions
You can also use SpellNumber for 14 currencies, as well as SpellNumber Sri Lankan Rupees.
If you have any queries or suggestions, please share them in the comment section below. We will be more than happy to assist you.
Pingback: COUNTIF Function: Excel, Sheets, Calc and Numbers - MSOfficeGeek
Pingback: CONCATENATE Function: Excel, Sheets, Calc & Numbers - MSOfficeGeek
Pingback: MIN Function: Excel, Sheets, Calc & Numbers - MSOfficeGeek
Pingback: DATEDIF Function: Excel, Sheets, Calc and Numbers - MSOfficeGeek