SpellNumber Indian Rupees Function is a custom Google Sheet Function manually created with a google 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 Sheet also doesn’t have an inbuilt function for converting numbers to words. Thus, a separate code was created in Google 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 SpellNumber Function for Indian Currency in Google Sheets.
6 Easy Steps To Create 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 Cntrl + A and delete everything.
5. Copy the following code in Code gs
This file contains 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 SpellNumberINR Function In Google Sheet?
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 to the column. The following results will be displayed:
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 Srilankan 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