Convert Numbers to Words using Indian Numbering in 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

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.

source:https://ift.tt/2zdK0Wn