5.6 Numbers Stored as Text
Signs: numbers are left-aligned, a green triangle appears, SUM returns 0 or a smaller total, =ISNUMBER(C2) is FALSE.
Before
| Order ID | Amount (text) |
|---|---|
| BLK-3201 | ₹1,299 |
| BLK-3202 | Rs. 450 |
| BLK-3203 | 1,25,000 |
| BLK-3204 | '64 |
| BLK-3205 | 90 INR |
=SUM() → 0
After
| Order ID | Amount |
|---|---|
| BLK-3201 | 1299 |
| BLK-3202 | 450 |
| BLK-3203 | 125000 |
| BLK-3204 | 64 |
| BLK-3205 | 90 |
=SUM() → 1,26,903
Steps in Excel – four ways
- Error button: select the cells with green triangles › click ⚠ › Convert to Number (works for plain numbers like '64).
-
Formula: remove symbols, then VALUE:
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"₹",""),"Rs.",""), "INR",""), ",",""))) -
Text to Columns: select the column › Data › Data Tools › Text to Columns › Delimited › Next › untick all delimiters › Next › General › Finish. Excel re-reads each cell as a number.
- Paste Special Multiply: type
1in an empty cell › copy it › select the text numbers › Home › Paste › Paste Special… › Multiply › OK. - Apply the rupee number format (1.5). Verify with
=SUMPRODUCT(--ISNUMBER(C2:C6))= 5.
Ravindra Bagale's Tip
Many students look at the total of the amount column and say "it's correct", but if half the cells are text, SUM skips them – the total comes out lower and nobody notices. Compare =COUNT() and =COUNTA(): if they differ, you have text-numbers. Clean numbers typed with ₹ and commas first, then apply the format.
Ravindra Bagale's Tip – मराठी
बरेच students amount column चा total बघून "बरोबर आहे" म्हणतात, पण अर्ध्या cells text असतील तर SUM त्यांना सोडून देतो – total कमी येतो आणि कोणालाच कळत नाही. =COUNT() आणि =COUNTA() compare करा: फरक असेल तर text-numbers आहेत. ₹ आणि comma type केलेले numbers आधी साफ करा, मग format लावा.
Ravindra Bagale's Tip – हिंदी
बहुत से students amount column का total देखकर "सही है" कह देते हैं, पर अगर आधी cells text हैं तो SUM उन्हें छोड़ देता है – total कम आता है और किसी को पता नहीं चलता. =COUNT() और =COUNTA() compare करो: फ़र्क हो तो text-numbers हैं. ₹ और comma के साथ type किए numbers पहले साफ़ करो, फिर format लगाओ.
Practice task
Clean ten amounts written as "₹1,299", "Rs 450/-", "1,25,000.00", "450 INR". Get a correct SUM and show it in Indian rupee format.