To turn text such as 1,23,456.78 into a real number, wrap SUBSTITUTE inside VALUE: =VALUE(SUBSTITUTE(C2,",","")). SUBSTITUTE removes every comma, whichever way the digits are grouped, and VALUE turns the remaining characters into a number you can add.
Why does the formula work on lakh-style grouping?
Indian grouping puts the first comma after three digits and then one after every two, so ₹1,23,456.78 is one lakh twenty-three thousand four hundred fifty-six rupees and seventy-eight paise. Excel does not care where the commas sit when you remove them all. After SUBSTITUTE the text reads 123456.78, which VALUE turns into the number 123456.78. You can show the lakh-style commas again later with a number format.
| Text in the cell | After SUBSTITUTE | After VALUE |
|---|---|---|
| 1,23,456.78 | 123456.78 | 123456.78 |
| 45,000 | 45000 | 45000 |
| 999.50 | 999.50 | 999.5 |
| 12,34,567 | 1234567 | 1234567 |
The formulas, from simplest to most forgiving
- =VALUE(SUBSTITUTE(C2,",","")) is the core version.
- =--SUBSTITUTE(C2,",","") does the same job. The double minus flips the sign twice, which forces text into a number without changing its value.
- =VALUE(SUBSTITUTE(TRIM(C2),",","")) also strips spaces at the start or end.
- =IF(C2="","",VALUE(SUBSTITUTE(TRIM(C2),",",""))) leaves blank rows blank rather than returning an error.
Fill the formula down beside the original column, check it, then copy the results and use Paste Special, Values over themselves. Only then delete the text column. Keeping the original until the last step means you can always go back.
Why not just use Find and Replace?
Ctrl+H, a comma in the first box, nothing in the second: it feels simpler, and on a column that is pure text it works. The risk shows up when the column is not pure. Excel's Find matches what a cell displays, so on cells that already hold true numbers with a comma format it can rewrite values you never meant to touch. A mixed column, half text and half numbers, is common in merged statements, and Replace changes both halves without telling you which was which. Once you close the file, there is no undoing it.
If you do use Replace, select only the text cells, take a copy of the sheet first, and compare totals before and after. A formula in a new column is slower to set up and much safer to repeat next month.
What if the result is #VALUE! or the total looks wrong?
Excel reports #VALUE! when something other than digits, a decimal point or a leading minus is left in the cell after SUBSTITUTE. The usual culprits are a rupee sign, the letters Dr or Cr, brackets around a negative, or a hidden space. Check by asking what is left: =SUBSTITUTE(C2,",","") on its own shows the leftover text. Fix the extra character, not the formula.
One more trap is your computer's regional setting. If it uses a comma as the decimal separator, a figure such as 999.50 may not convert the way you expect. Test with a number you know.
Is there a way to make this automatic next month?
Keep the formula in a column of an Excel Table. Paste next month's raw amounts into the text column and the formula copies itself down the new rows. If you work in Google Sheets, the same formula works as written, and ARRAYFORMULA can fill a whole column from one cell.
Closing checklist
- Original amounts kept in their own column.
- Helper column with SUBSTITUTE and VALUE filled down.
- ISNUMBER test returns TRUE for every row that has an amount.
- SUM of the helper column ties to the statement total.
- Paste as values, then delete the text column.
If the same cleanup is eating your Sundays, SpendixAI can take the statement as a CSV or Excel upload and show the rows for review before saving, so commas never become your problem.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


