Numbers stored as text are a classic Excel problem. The cells look like numbers, but SUM returns zero, sorting puts 100 before 9, and VLOOKUP fails to match. This usually happens when data is imported from a website, a CSV file or another system, or when a cell is formatted as Text before you type in it. The fix is quick once you know the right tool. This guide shows seven reliable methods, how to spot text numbers, how to fix hidden spaces and non-breaking characters, how to handle different decimal formats, and how to avoid the problem in future.
Updated November 2026
In this guide
- How to tell if a number is stored as text
- Method 1: Convert to Number from the warning icon
- Method 2: Text to Columns
- Method 3: Paste Special Multiply
- Method 4: the VALUE function
- Method 5: the double unary or math trick
- Method 6: NUMBERVALUE for other decimal formats
- Method 7: Power Query
- Which method to use
- Fix hidden spaces and special characters
- Practice problems
- Quick recap
- Worked example
- How to prevent the problem
- Common mistakes
How to tell if a number is stored as text
Text numbers are usually left-aligned in a cell, while real numbers are right-aligned. A small green triangle often appears in the top-left corner of the cell. The status bar at the bottom of Excel shows only a Count, not a Sum, when you select text numbers. You can test a cell with =ISNUMBER(A2), which returns FALSE for text. You can also check the length with =LEN(A2) to see whether hidden spaces make the cell longer than it looks. If SUM returns zero for a column of numbers, text is the most likely cause.
Method 1: Convert to Number from the warning icon
Select the cells with the green triangle, click the yellow warning icon that appears and choose Convert to Number. Excel converts all selected cells at once. This is the fastest method when the green triangle shows. If the warning icon does not appear, the cells may have been cleaned of the error flag, or the text includes spaces that Excel cannot convert, so use one of the other methods.
Method 2: Text to Columns
Select the column, go to Data, then Text to Columns. Click Next twice and then Finish, without changing any options. Excel re-reads each cell and converts those that look like numbers. This is a simple fix for a single column, and it works even when there is no green triangle. If the numbers have a different decimal or thousands separator, click Advanced in step 3 and set the separators before you finish.
Method 3: Paste Special Multiply
Type 1 in an empty cell and copy it. Select the text numbers, right-click, choose Paste Special, pick Multiply and click OK. Excel multiplies each cell by 1, which forces a conversion to a number. Delete the helper cell afterward. This method works on large ranges and keeps the numbers in place, so you do not need a new column. It will not fix text with spaces or letters, since those cannot be multiplied.
Method 4: the VALUE function
The VALUE function converts a text string that looks like a number into a number. =VALUE(A2) turns “123” into 123. It also handles percent text and dates, so =VALUE("25%") returns 0.25. If the cell contains text that cannot be a number, such as “abc”, VALUE returns #VALUE!. Wrap it in IFERROR to keep your sheet tidy: =IFERROR(VALUE(A2),""). Fill down, then copy and paste as values to replace the original column.
Method 5: the double unary or math trick
You can convert text with an operation that forces a number. =--A2 (two minus signs) or =A2*1 or =A2+0 all return a real number from text such as “45”. These formulas are short and fast and are often used inside larger formulas, such as =SUMPRODUCT(--(A2:A10="Yes"),B2:B10). The result is identical to VALUE for simple numbers, so use whichever you find easier to read.
Method 6: NUMBERVALUE for other decimal formats
Data from other countries may use a comma as the decimal separator and a period as the thousands separator, such as “1.234,56”. NUMBERVALUE handles that: =NUMBERVALUE("1.234,56",",",".") returns 1234.56. The second argument is the decimal separator and the third is the group separator in the text. NUMBERVALUE is available in Excel 2013 and later. Use it for exports from European systems or for cells where VALUE gives errors because of your regional settings.
Method 7: Power Query
For repeated imports, use Power Query. Select the data, choose Data, then From Table/Range. In the editor, click the column header icon and choose Decimal Number or Whole Number to change the data type. Choose Using Locale if the data uses a different format. Close and Load. Next time, refresh the query and the numbers will convert automatically, even when the source changes.
Which method to use
This table helps you choose.
| Situation | Best method |
|---|---|
| Green triangle on cells | Convert to Number |
| One column, no warning | Text to Columns |
| Large range, keep in place | Paste Special, Multiply by 1 |
| Need a formula | VALUE or double unary |
| Comma decimal format | NUMBERVALUE |
| Weekly imports | Power Query |
Fix hidden spaces and special characters
If the methods above return errors, the text may contain spaces or special characters. Clean it first with =VALUE(TRIM(A2)) to remove normal spaces. If the data came from a web page, the space may be a non-breaking space, character 160, so use =VALUE(SUBSTITUTE(A2,CHAR(160),"")). For currency symbols, remove them too: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")). For numbers with a trailing minus, such as “100-“, used by some accounting systems, use =IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)). See our guide to removing spaces in Excel for more on hidden characters.
Practice problems
Practice on a fresh sheet. Type the text values “450”, “1,200” and “75.5” into column A after formatting it as Text, so they are stored as text. Use =ISNUMBER(A1) to confirm each returns FALSE. Now apply Text to Columns and check again, which should return TRUE. Next, type ” 300 ” with spaces on both sides and try =VALUE(A4). Excel can often handle plain spaces, but if it fails, use =VALUE(TRIM(A4)). Finally, paste a value copied from a web page and test with =LEN(A5) compared with the visible digits. If the length is longer, a hidden character is present, and SUBSTITUTE with CHAR(160) will remove it.
Another useful exercise is to build a check column. In B1 enter =IF(ISNUMBER(A1),"OK","Text") and fill down. Filter for Text to find every problem cell in a long list quickly. This is a good habit before you send any workbook to a client, because totals that silently ignore text numbers are one of the most common reasons for wrong reports.
Quick recap
Text numbers are left-aligned, ignored by SUM and flagged with a green triangle. Convert them with the warning icon, Text to Columns, Paste Special Multiply, VALUE, the double unary, NUMBERVALUE or Power Query. Clean spaces with TRIM and CHAR(160) with SUBSTITUTE. Confirm the fix with ISNUMBER, and compare COUNT with COUNTA to make sure nothing is left as text.
Worked example
A column has the text values “1,250”, “980” and “2,100.50” imported from a report. SUM returns 0. Select the column and use Text to Columns, then Finish. Excel converts them to 1250, 980 and 2100.5, and SUM now returns 4,330.5. If the values come with a space, such as “1 250”, first use Find and Replace to remove the space. If a few cells remain as text, check them with ISNUMBER and handle them with VALUE. Finally, format the column as Number with two decimals so the display is consistent, and add a check cell that compares the count of numbers with the count of rows, using =COUNT(A2:A100) and =COUNTA(A2:A100). If they differ, some cells are still text. Our guide to COUNTIFS shows how to count by condition.
How to prevent the problem
Format cells as General or Number before typing, not as Text. Avoid typing an apostrophe at the start, since it forces text. When you import CSV files, use Data, then From Text/CSV, which lets you set column types. Ask the person who provides the data for numeric fields without currency symbols or spaces. When you share a template, protect the input cells and add data validation that allows only numbers, so users cannot type text in a number field.
Common mistakes
The most common mistake is to change the cell format to Number after the text is there, which does not convert the contents. You must re-enter or convert the values. Another is to convert part of the data only, leaving a few text numbers that cause totals to be wrong. A third is to forget about dates stored as text, which need DATEVALUE rather than VALUE. A fourth is to convert and then delete the source before pasting values. Finally, always check the result with ISNUMBER, and compare totals with the source before you rely on the numbers in a report.
Frequently asked questions
Why does Excel treat my numbers as text?
They may have been imported, formatted as Text before typing, or contain spaces or an apostrophe.
What is the fastest way to convert text to numbers?
Use Convert to Number from the warning icon, or Text to Columns for a whole column.
How do I convert text to number with a formula?
Use =VALUE(A2) or =–A2.
Why does VALUE return #VALUE!?
The text contains characters that are not part of a number, such as spaces or letters. Clean it with TRIM or SUBSTITUTE.
How do I convert numbers with a comma decimal?
Use NUMBERVALUE, for example =NUMBERVALUE(A2,”,”,”.”).


