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.

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

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

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.