Amounts that sit on the left of the cell, wear a small green triangle, or make SUM return 0 are numbers stored as text. The quickest bulk fix is to select the whole column and use the warning icon's Convert to Number, or run Text to Columns and click Finish. If hidden characters block both, a VALUE formula in a helper column is the fallback.
How do you know a statement column is text and not numbers?
Bank portals and PDF-to-Excel tools often hand over amounts as text, because the cell holds the characters "1,250.00" rather than the number one thousand two hundred fifty. Excel cannot add characters, so your totals quietly go wrong. Look for these signs:
- The amounts hug the left edge of the cell instead of the right.
- A green triangle sits in the corner, and the warning icon says "Number Stored as Text".
- Select the column and the status bar at the bottom shows Count but no Sum.
- =SUM(C2:C500) returns 0, or a total far smaller than the statement.
- =ISNUMBER(C2) returns FALSE for a cell that plainly shows a figure.
Four bulk fixes side by side
None of these needs a formula per row except the last. Pick by how messy your column is.
| Method | What you do | Works well when | Watch out for |
|---|---|---|---|
| Error-check menu | Select the cells, click the warning icon, choose Convert to Number | Cells carry green triangles | The icon only appears if Excel flags the cells; it may not appear on very large selections |
| Text to Columns | Select the column, Data, Text to Columns, Next, Next, General, Finish | One column, no extra characters | Run it on one column at a time; it will not remove a Dr suffix or a rupee sign |
| Paste Special multiply | Type 1 in a blank cell, copy it, select the amounts, Paste Special, Multiply | Triangles are missing but cells are clean | Cells with stray spaces turn into #VALUE! errors |
| VALUE in a helper column | =VALUE(TRIM(C2)) filled down, then copy and paste as values | Spaces or odd characters around the figure | You must paste values and delete the old column afterwards |
Step by step: Text to Columns on the whole column
This is the one most people reach for, because it needs no formula and handles thousands of rows in a second.
- Take a copy of the sheet first so you can compare afterwards.
- Click the column letter to select the amount column. Do not select the header row separately.
- Go to the Data tab and click Text to Columns.
- Choose Delimited, press Next, tick nothing in the delimiter list, press Next again.
- Leave the column data format on General and click Finish.
- Format the column with two decimals so rupees and paise line up.
How can you confirm every cell really converted?
Do not trust the look of the column. Run two small checks in empty cells off to the side:
- =SUMPRODUCT(--ISTEXT(C2:C500)) counts cells still holding text. You want 0.
- =COUNT(C2:C500) counts true numbers. Compare it with =COUNTA(C2:C500), which counts everything non-empty. They should match.
- =SUM(C2:C500) against the closing total printed on your statement, entered by hand in another cell.
Say the statement has 142 transaction rows. COUNT should read 142 once converted. If it reads 139, three cells are still text, usually because of a trailing space, a rupee sign or a Dr/Cr suffix. Filter the column with a formula such as =ISTEXT(C2) to see exactly which rows.
What if the amounts still will not convert?
Then something other than digits is in the cell. A leading apostrophe, a trailing minus, a currency sign, or a non-breaking space copied from a web page will all stop Excel from reading the cell as a number. Use LEN to compare the characters you can see with the characters that exist: a cell showing 1,250.00 should have a length of 8. A length of 9 means an invisible character is hiding in there.
A short checklist before you build on the column
- Amounts right-aligned and COUNT equal to COUNTA.
- SUM matches the statement's own total, or the difference is explained.
- Number format set to two decimals, not Text.
- Original text column kept hidden or on a spare sheet until you are happy.
When you are doing this for every month of every account, a sheet gets tiring. SpendixAI accepts statements as PDF, CSV or Excel and lets you review the rows before saving, so amounts arrive as numbers; layouts differ, so check the rows it shows you.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


