Convert Numbers to Words using Indian Numbering in Google Sheets
Use a custom Google Sheets function to convert numbers to words in Indian Rupees using lakhs and crores grouping.
The Indian numbering and currency system widely uses lakhs and crores for writing large numbers. The term lakh
means one hundred thousand while a crore
represents ten million. A billion is written as hundred crores and there exist even higher denominations like arab
, kharabh
or neel
which is equivalent to 10 trillion.
If you would like to write financial numbers in Google Sheets using the Indian Numbering system (lakhs, crores), here’s a custom Sheets function INR()
that can spell the numeric value in words for you.
Also see: Make Invoices with Google Sheets and Forms
Convert Numbers to Indian Rupees in Google Sheets
To get started, go to your Google Sheet, click the Tools menu and select Script Editor. Copy-paste the function below inside the Script Editor and chose File > Save to save your changes.
Now switch to the Google Sheet, type =INR(123)
in any cell and it will instantly spell the number in words using the lakhs and crores system. The function was originally written by Amit Wilson and adopted to use the V8 Runtime.
/**
* Convert number to words in Indian Rupees
*
* @param {number} input The value to convert.
* @return The number in lakhs and crores.
* @customfunction
*/
function INR(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 '';
}
The Google Sheets function will only be available in the spreadsheet where you have added the above code. If you create a copy of the spreadsheet, the function would be copied as well.
Amit Agarwal
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.
Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory