Convert Numbers to Words using Indian Numbering in Google Sheets

C

Use a custom Google Sheets function to convert numbers to words in Indian Rupees using lakhs and crores grouping.

Published in: Javascript - Google Sheets

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

Indian Rupee in Google Sheets

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.

📮  Subscribe to our Email Newsletter for Google tips and tutorials!
Published in: Javascript - Google Sheets

Looking for something? Find here!

Meet the Author

Web Geek, Google Developer Expert
A
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. He is the developer of Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

Get in touch

Google Add-ons

Do more with your Gmail and GSuite account
G

We build bespoke solutions that use the capabilities and the features of Google Workspace for automating business processes and driving work productivity.

  1. Mail Merge with Attachments
    Send personalized email to your Google Contact with a Google Sheet and Gmail
  2. Save Emails and Attachments
    Download email messages and file attachments from Gmail to your Google Drive
  3. Google Forms Email Notifications
    Send email notifications to multiple people when a new Google Form is submitted
  4. Document Studio
    Create beautiful pixel perfect documents merging data from Google Sheets and Google Forms
  5. Creator Studio for Google Slides
    Turn your Google Slides presentations into animated GIFs and videos for uploading to YouTube