Spreadsheets

Remove the rupee symbol or INR from amount cells before summing

Remove the rupee symbol or INR from amount cells in Excel with SUBSTITUTE or Find and Replace so SUM works, and when a number format is better.

Editorial illustration for Remove the rupee symbol or INR from amount cells before summing

If amounts read ₹1,250.00, Rs. 1,250 or INR 1,250.00, Excel is holding text. Strip the prefix and the commas with a nested SUBSTITUTE inside VALUE, or use Find and Replace on a copy. If the cell is already a true number that merely displays a rupee sign, there is nothing to remove: that is just a number format.

First, is it text or just formatting?

This one test saves a lot of work. Click the cell and look at the formula bar. If the bar shows 1250 or 1250.5 with no rupee sign, the cell is a real number and the ₹ comes from formatting; SUM will already work. If the bar shows ₹1,250.00, Rs. 1,250 or INR 1,250.00, the sign is part of the text and has to go. Right-aligned cells are usually numbers, and left-aligned ones usually text.

The formula for rupee, Rs. and INR prefixes

SUBSTITUTE is case-sensitive, so convert the text to capitals first. Then remove each prefix, remove commas, trim and convert:

  • =VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(C2),"₹",""),"RS.",""),"INR",""),",","")))
  • If some rows say Rs without a full stop, add another SUBSTITUTE for "RS" after the "RS." one, never before it, otherwise the full stop is left behind.
  • A leading or trailing minus on the text needs separate handling; the formula above would not read it as negative.
Text in the cellResultWhy
₹1,250.001250Sign and comma removed
Rs. 18,00018000Converted to RS. then removed, space trimmed
INR 52,000.0052000INR removed, space trimmed
rs.450450UPPER makes the match work

Find and Replace, when you prefer a quick pass

Select the amount column and press Ctrl+H. Type ₹ in Find what, leave Replace with empty, and press Replace All. Repeat for Rs. and INR. To enter the ₹ symbol, copy it from one of the cells or use the character map on your keyboard.

Two cautions. First, do this on a copy: Replace All cannot name the rows it touched. Second, finishing the replacement does not always convert the cell. Some cells will stay as text, so follow up with Text to Columns or a multiply-by-1 paste, and then check with =SUMPRODUCT(--ISTEXT(C2:C500)), which should return 0.

When is a currency number format the better answer?

If you want the rupee sign to show on your report, do not store it in the cell at all. Keep the cell a number and let the format draw the sign. Select the cells, press Ctrl+1, choose Currency and pick the rupee symbol from the Symbol list, or use a custom format such as ₹#,##0.00.

Indian lakh grouping is not Excel's default, but this custom format approximates it for positive amounts: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0. Paste it into Format Cells, Custom. Negative amounts need a variation, so test on your own data, and remember that the format changes only how the number looks, never its value.

What if the currencies are mixed?

A statement that mixes ₹ with USD or another currency should not be stripped blindly, because removing the code makes 40 USD look like 40 rupees. Split the currency into its own column first, for example =LEFT(TRIM(C2),3) when the code comes first, then convert only the figure. Totals across currencies need a rate for each row, and that rate belongs in the sheet too.

A worked example with hypothetical figures

Say a month has five lines: ₹1,250.00, Rs. 18,000, INR 52,000.00, rs.450 and ₹2,350. After cleaning they become 1250, 18000, 52000, 450 and 2350. The SUM is 74,050. If the formula returns anything else, find the row with =ISNUMBER(D2) set to FALSE and look at its characters.

Quick checklist

  • Look at the formula bar to see if the symbol is real text.
  • Clean in a helper column, never over the original.
  • COUNT should equal COUNTA after cleaning.
  • Apply a number format for display afterwards.

SpendixAI accepts statements as PDF, CSV or Excel and shows rows for review before you save them, so you do not have to strip symbols by hand every month.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports