Numbers Stored as Text in Excel: Find and Fix

Numbers stored as text in Excel look like ordinary numbers, but Excel treats them as words. SUM skips them, lookups fail to match them and sorting puts them in the wrong order, often with no error message at all. This guide shows you how to find them, the fastest ways to convert them, and what to do when the usual fix doesn't work.

What does "number stored as text" mean?#

A number stored as text is a cell that holds digits, like 142, but is saved as a piece of text rather than a value Excel can calculate with. To you, it looks the same. To Excel, "142" (text) and 142 (number) are different things.

This matters because most functions only use real numbers. SUM adds numbers and skips text, so a column with a few text numbers gives a total that is too low. VLOOKUP and XLOOKUP treat the text "1001" and the number 1001 as different values, so they return #N/A.

How to spot numbers stored as text#

You can usually see them without any formula. Look for these signs:

The green triangle is Excel's error checking. Select the cell and a yellow warning icon appears beside it; hover over it and it says the number in this cell is formatted as text or preceded by an apostrophe.

To count text numbers in a column, compare two counts:

=COUNTA(B2:B6)-COUNT(B2:B6)

COUNTA counts every filled cell. COUNT counts only real numbers. The difference is the number of filled cells that aren't numbers. You practise this check in the Debugging SUM lesson.

Why numbers end up stored as text#

Text numbers usually come from somewhere else. The common causes are:

• Imported or copied data. Exports from accounting systems, CRMs and web pages often arrive as text.
• The Text number format. If a cell is formatted as Text before you type, anything you type is stored as text.
• A leading apostrophe. Typing '142 tells Excel to keep the entry as text. The apostrophe doesn't show in the cell, only in the formula bar.
• Hidden characters. A trailing space, or a non-breaking space copied from a web page, makes 142 text.
• Text formulas. LEFT, RIGHT, MID and TEXT always return text, even when the result is all digits.

How to convert numbers stored as text (5 ways)#

The best method depends on how many cells you have and whether they contain hidden characters.

1. Convert to Number (fastest for a few cells)#

1. Select the cells with green triangles. Start the selection on a flagged cell.
2. Click the yellow warning icon that appears.
3. Choose Convert to Number.

This works on Windows and Mac. If you don't see the icon, error checking may be turned off, so use one of the methods below.

2. Text to Columns (good for a whole column)#

1. Select the column.
2. Go to Data > Text to Columns.
3. Click Finish straight away.

Excel re-reads every cell as General, which turns text digits into numbers. The Text to Columns guide explains the rest of the wizard.

3. Paste Special Multiply by 1#

1. Type 1 in an empty cell and copy it (Ctrl+C, Mac: ⌘C).
2. Select the text numbers.
3. Open Paste Special (Ctrl+Alt+V on Windows, Control+⌘V on a Mac), choose Multiply and click OK.

Multiplying text digits by 1 forces Excel to treat them as numbers. Delete the 1 afterwards.

4. VALUE in a helper column#

When you want a formula, use VALUE:

=VALUE(B3)

VALUE turns text digits into a real number. Fill it down, then copy the helper column and paste it back as values if you want to replace the original. The TEXT and VALUE lesson has you do this on imported unit counts.

5. Retype or reformat#

For one or two cells, set the format to General and type the number again. Changing the format alone does not convert the value; you have to re-enter it.

When the usual fixes don't work#

If Convert to Number does nothing, or VALUE returns #VALUE!, the text contains a character Excel can't read as a number. The two usual culprits are spaces and non-breaking spaces (character 160, common in data copied from web pages).

Clean the text first, then convert it:

=VALUE(TRIM(SUBSTITUTE(B3,CHAR(160),"")))

SUBSTITUTE removes the non-breaking spaces, TRIM removes ordinary extra spaces, and VALUE converts the result. If VALUE still fails, the cell may hold a currency symbol or thousands separator from a different region. Our guide to the #VALUE! error covers more of these cases.

How to add up text numbers without converting them#

Sometimes you can't change the source data, for example when it is refreshed from another system every day. Then convert inside the formula:

=SUMPRODUCT(--B2:B6)

The double minus (--) turns each value into a number: text digits become numbers, and real numbers stay the same. SUMPRODUCT then adds them up. On this data, =SUM(B2:B6) returns 555 because it skips the text 45 and 210, while =SUMPRODUCT(--B2:B6) returns the correct 810.

One limit: a blank cell or a word in the range makes -- return #VALUE!, so use this only on a range that holds digits. For more on totals that come out wrong, see why your Excel total is wrong, and the SUM function guide for the full reference.

When text is the right choice#

Not every text number is a mistake. Codes such as ZIP codes, employee IDs like 00123 and phone numbers should stay text, because you never calculate with them and leading zeros matter. Store them as text on purpose, and keep the lookup column in the same type. To format real numbers for display without changing them, use the TEXT function and format codes instead.

Practise it#

Try the exercise on this page, then practise turning text digits into real numbers with VALUE in a live spreadsheet. The Debugging SUM lesson walks you through finding a text number that breaks a total, the way a job test might.