Signs your numbers are stored as text
A number stored as text looks normal, but Excel treats it as a word. The usual symptoms:
- Totals are wrong.
=SUM(C2:C500)returns 0 or a figure far too small, because SUM ignores text cells in a range. - Counts are wrong.
=COUNT(C2:C500)counts only real numbers, so it reports fewer rows than you can see. - Lookups fail. VLOOKUP or XLOOKUP returns #N/A because the number 123 and the text “123” are not equal.
- Sorting is odd. Text sorts character by character, so 1000 lands before 20.
- Green triangles appear in the top-left corner of cells, and the values are left-aligned.
- Pivot tables show Count instead of Sum when you drop the column into Values.
Why it happens
Most of the time the data was never typed into Excel. It came from somewhere else:
- Currency and labels in the cell. Exports write “₹1,00,000”, “Rs. 12,34,567” or “$1,299.99”. Excel cannot treat a cell containing a symbol or a word as a plain number.
- Accounting formats. Tally, SAP and many accounting systems show negatives as (2,500.00) or with a trailing minus, 1,250-.
- Copying from websites and PDFs. Web pages often use a non-breaking space between digits or before a currency sign, and a Unicode minus sign (−) that looks like a hyphen but is a different character.
- Columns formatted as Text. If a column was set to Text before data was pasted in, everything stays text, even plain digits.
- CSVs imported with types turned off. Importing every column as text keeps codes safe but leaves amounts unusable for sums.
How to convert text to numbers manually in Excel
The error indicator
Select the cells with green triangles, click the warning icon that appears and choose Convert to Number. It is quick for small ranges, but it only works on values Excel already recognises as numbers. Cells containing ₹, Rs or a trailing minus are not flagged.
Paste Special › Multiply
- Type 1 in an empty cell and copy it.
- Select the text numbers.
- Choose Home › Paste › Paste Special, select Values and Multiply, and click OK.
Multiplying by 1 (or adding 0) forces Excel to re-read each value. As with the error indicator, cells with symbols or words are left alone.
Text to Columns
Select one column, choose Data › Text to Columns and click Finish straight away. Excel re-enters every value, which converts plain text numbers. In step 3, the Advanced button has a Trailing minus for negative numbers setting, so values like 1,250- can be handled here too.
Formulas
=VALUE(A2) converts text that looks like a number. For European formats with a comma as the decimal mark, =NUMBERVALUE(A2, ",", ".") reads 1.234,50 as 1234.5 regardless of your computer’s settings.
When the cell carries a currency label, strip it first:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"Rs.",""),"₹",""))
Every extra symbol or code needs another SUBSTITUTE. Text copied from the web may also contain a non-breaking space, which TRIM does not remove; wrap the cell in SUBSTITUTE(A2,CHAR(160),"") to get rid of it. After the formulas work, copy the helper column and use Paste Values over the original.
Where this tool helps
Bank and payment-gateway exports. Settlement reports often include the currency symbol in every amount, so a month of transactions will not total until the symbols go.
Accounting exports. Ledgers from Tally or SAP with brackets and trailing minus signs need converting before you can reconcile them against other records.
Data copied from websites and PDFs. Price lists and statements pasted into Excel bring hidden spaces and lookalike minus signs with them.
Preparing a pivot table. Converting first means the pivot sums amounts instead of counting them, and filters by value work as expected.
What the tool does with each value
- Currency symbols and codes are removed wherever they sit: ₹, $, €, £, ¥, Rs, Rs., INR, USD, EUR, GBP and US$.
- Grouping is checked, not just stripped. 1,00,000 and 1,234,567 are read correctly; a value like 1,2,3 stays as text.
- Negatives in every common style: -45, (45), 45- and −45.
- Percentages become 0.125 for 12.5% by default, which is how Excel stores them. Format the column as a percentage to show 12.5% again, or choose the option that gives 12.5.
- Scientific notation such as 1.5E+03 becomes 1500.
- Long numbers and codes are protected. Anything over 15 significant digits stays text, and codes such as 00123 keep their zeros unless you turn that switch off.
The summary tells you what happened, for example: “Converted 14 cells in Amount, GST rate and Balance to numbers. 1 cell in those columns is not a number and was left as text: C6 “N/A”.”