Excel treats "1001" and 1001 as different values. A number stored as text can look normal, but SUM ignores it, COUNT does not count it, and an exact-match VLOOKUP cannot find it in a numeric column.
Quick answer: test with =ISNUMBER(A2). If it returns FALSE, the value is text. Select the column and use Data → Text to Columns → Finish to convert it in place. If that fails, use =VALUE(A2) or =--A2 in a helper column, then paste the values back.
Match the Symptom to the Cause
| What you see | Likely cause | First fix |
|---|---|---|
SUM returns 0 | Every value in the sum range is text | Convert the sum range to numbers |
COUNT returns 0 but COUNTA is not 0 | Values are text numbers | Use Text to Columns or a helper conversion |
| Green triangle in the corner | Excel's “Number Stored as Text” warning | Convert, unless it is an ID with leading zeros |
VLOOKUP returns #N/A | Lookup value is text, table key is numeric | Standardise both sides of the match |
| Numbers are left-aligned | Possible text numbers | Confirm with ISNUMBER before converting |
VALUE returns #VALUE! | Hidden spaces, currency symbols or wrong separators | Clean the text, then convert |
1. Diagnose With ISNUMBER and COUNT
Put these checks beside the column before you change anything:
=ISNUMBER(A2)— TRUE for a real number, FALSE for text.=ISTEXT(A2)— TRUE for text, including a text number.=COUNT(A2:A20)— counts real numbers only.=COUNTA(A2:A20)— counts non-empty cells, including text.=SUM(A2:A20)— ignores text numbers in the range.
If COUNT is lower than COUNTA, some values are text. Left-alignment and the green triangle are clues, but the formulas are the proof.
2. Convert a Whole Column With Text to Columns
This is the quickest fix for pasted or imported numbers:
- Select the column, including the blank cells below the data.
- Go to Data → Text to Columns.
- Choose Delimited and click Next twice.
- Keep the column format as General and click Finish.
- Check the result with
=ISNUMBERand=COUNT.
If the values are IDs with meaningful leading zeros, do not convert them. Keep the ID as text and convert the lookup key instead.
3. Convert With a Helper Formula
Use a helper column when you need to document the conversion or avoid changing the source:
=VALUE(A2)— converts text that uses your regional number format.=--A2— double unary; shorter, but harder to read for a marker.=A2*1— same idea, easy to explain.=NUMBERVALUE(A2)— available in Excel 2013 and later; respects the text as written.
Copy the helper, then use Paste Special → Values over the original column. Check a few rows first, especially blank rows and zeros.
4. Remove Spaces and Hidden Characters First
A number copied from a web page can contain a non-breaking space or a line break. Excel sees text, not a number. Clean it before converting:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))
For a whole pasted column, the Data Cleaner can strip spaces, quotes and line breaks in one pass. Then convert the cleaned column with Text to Columns.
5. Handle Currency, Commas and Percentages
If the text contains a currency symbol or thousands separators, plain VALUE may fail depending on your region. Use NUMBERVALUE with explicit separators:
=NUMBERVALUE(A2,".",",")— US-style1,234.56.=NUMBERVALUE(A2,",",".")— European-style1.234,56when the decimal separator is a comma.=NUMBERVALUE(SUBSTITUTE(A2,"$",""),".",",")— remove a dollar sign from a US-style value first.- For percentages, strip the
%and divide by 100, or let VALUE handle a recognised percent string.
Check the decimal separator in the original file. Swapping it the wrong way changes 1,234 from one thousand to one point two three four.
6. Fix Lookup Failures Caused by Text vs Number
Exact-match lookups compare data types. Text "1001" does not equal numeric 1001, even though they look identical. Standardise both sides:
- Convert the lookup key:
=VLOOKUP(VALUE(A2),Table,2,FALSE). - Or convert the table key once, then use a normal exact-match lookup.
- Use
TRIMon both sides when copied names or IDs may contain trailing spaces. - Use
=XLOOKUP(A2,Table[ID],Table[Value],"Not found",0)after both columns share a type.
If the lookup still fails, the #N/A guide walks through duplicates, hidden spaces and approximate-match settings.
7. Fix SUMIFS and COUNTIFS Totals
SUMIFS sums only numeric cells in the sum range. If the amounts are text, the criteria can match but the total is zero or too low. Convert the sum range, not just the criteria column.
COUNTIF and COUNTIFS can behave inconsistently when criteria and ranges mix text and numbers. The reliable fix is to make both sides the same type before you build the criteria. Read the COUNTIF and SUMIFS guide for the criteria syntax.
Prevent It Next Time
- Format the destination column as Number before pasting.
- Import CSV files through Power Query and set the type explicitly.
- Use Paste Special → Values for formula results; it still will not turn text into numbers.
- Do not type an apostrophe before a number unless you need a text ID.
- Add a check cell with
=COUNT(range)beside important totals.
Before You Submit
Check every numeric column with ISNUMBER, then compare SUM with a calculator for one category. If a lookup is involved, test a key that starts with a zero and a key that does not. Convert the column before you build the final chart or PivotTable.
Use the Data Cleaner for pasted columns, the Formula Checker for the lookup syntax, and the Workbook Health Check for the formulas that depend on the converted data.
Related: VLOOKUP examples, dates not sorting, formula not calculating, and the Excel troubleshooting hub.
