Excel Not Recognizing Numbers? Fix Text-Formatted Cells

Errors & fixes · Updated November 18, 2026

Excel usually fails to recognize numbers because the cells contain text that looks numeric, often after a paste or file import. Select the affected cells and use the warning icon’s Convert to Number command; if there is no warning icon, try Data > Text to Columns and finish the wizard without splitting the column.

Text-formatted numbers can make totals look wrong, prevent arithmetic, and sort in an unexpected order. The right fix depends on what is in the cells: an ordinary digit string is easy to convert, while a value with a currency symbol, hidden space, decimal separator, or leading zero may need special handling.

Why Excel treats numbers as text

Excel stores numbers and text as different kinds of values. A cell can display the characters 1250 while holding either the numeric value 1250 or the text string “1250.” The two may look identical on screen, but Excel does not treat them identically in calculations and other operations.

Common causes include pasting from a website, exporting or opening a CSV file, setting the cells to the Text number format before entering data, and importing data whose decimal or thousands separators do not match your computer’s regional settings. A leading apostrophe can also tell Excel to store an entry as text. In some cases, extra spaces or nonprinting characters make an otherwise numeric string impossible to convert directly.

Formatting and value are separate. Applying the Number format changes how a numeric value is displayed; it does not reliably turn text into a number. That is why choosing Home > Number Format > Number may leave a text-formatted value unchanged.

How to tell whether a cell contains text

Look for a small green triangle in the cell’s upper-left corner. When Excel flags a value as a number stored as text, selecting the cell may show a warning icon with a conversion option. This indicator is useful, but its absence does not prove that every value is numeric; error-checking settings and the specific contents can affect what Excel flags.

Other clues include a number that stays left-aligned when nearby numbers are right-aligned, a total that is smaller than expected, or a sort that groups some digit strings separately from numeric values. Alignment is only a clue because users can change alignment manually. To check more directly, select a cell and inspect the formula bar and number format. You can also test behavior in a spare cell: with the suspected value in A2, entering =ISNUMBER(A2) returns FALSE for text and TRUE for a numeric value. This check does not repair the original cell.

Pay attention to the symptom. If a value is numeric but merely displays without decimals or with a currency symbol, it may need a display-format change, not conversion. If it is text, changing the display format alone will not solve the underlying issue.

Quick ways to convert text-formatted numbers

In Excel for Microsoft 365 on Windows, first try the error-checking option:

  1. Select one flagged cell or a group of flagged cells.
  2. Click the warning icon that appears beside the selection.
  3. Choose Convert to Number, then check a few results and recalculate or review any affected totals.

If the warning is missing, Text to Columns is often a useful bulk conversion for a simple, single column of digit strings. Select the cells, choose Data > Text to Columns, proceed through the wizard without choosing a delimiter that would split the values, and select Finish. Check the result afterward, especially if the data contains leading zeros or unusual separators. The Mac desktop version has a similar command, though ribbon layout can differ. If you cannot find the command in Excel for the web, open the workbook in the desktop app.

Another option for clean numeric text is Paste Special with multiplication. Type 1 in an unused cell and copy it. Select the text-number cells, then use Home > Paste > Paste Special and choose Multiply. This coerces compatible text values into numbers. It is a bulk edit, so use it only on a suitable range and verify the values; it can be unsuitable when the cells contain labels, IDs, or mixed content.

Keep a copy of the original column before a large conversion. That makes it easier to compare the converted data and recover values if Excel interprets a separator or date differently than intended.

Use a formula when values need cleaning

A formula can convert a straightforward numeric string while leaving the source unchanged. With the text value 1250 in A2, enter the following in B2:

=VALUE(A2)

The result is the number 1250, provided Excel can interpret the text as a number under the current regional settings. Fill the formula down for other rows, check the results, and then, if you need to replace the original column, copy the results and use Paste Special > Values. A formula result is not the same as changing the original cell in place.

For a value with nonstandard separators, specify which character marks the decimal and which marks groups of thousands. With the text 1.234,50 in A2, use:

=NUMBERVALUE(A2,",",".")

This returns the numeric value 1234.5: the comma is treated as the decimal separator and the period as the group separator. NUMBERVALUE is available in current Excel versions, including Microsoft 365. Confirm the separators in the source data before using it; otherwise, a value can be interpreted at the wrong scale.

Formulas cannot safely infer every intended meaning. For example, a string such as 03/04/2026 could represent different dates in different regional formats. Confirm the source convention instead of assuming which day and month the text represents.

Check spaces, symbols, and imported separators

When conversion fails, inspect the actual characters rather than repeatedly changing the cell format. Pasted values can contain leading or trailing spaces, nonbreaking spaces, currency symbols, or characters that resemble ordinary punctuation but are different characters. A space used as a thousands separator can also be valid in one source format and unexpected in another.

  • Spaces: Check whether there is a visible gap before or after the digits. Nonbreaking spaces may look like normal spaces but resist ordinary cleanup.
  • Separators: Determine whether the source uses a comma or period for decimals and what it uses to group thousands.
  • Symbols: A value such as “$1250” or “1250 kg” is not just a plain numeric string. Remove or handle the symbol or unit only if you know it is not part of the information you need to preserve.
  • Signs and parentheses: Imported accounting data may represent a negative amount with parentheses, which may need deliberate conversion.

For a recurring CSV workflow, use Data > From Text/CSV in the Windows desktop version of Excel to preview the file before loading it. Check the preview for column alignment and decimal interpretation. In the import or Power Query steps, set a suitable data type when appropriate, and verify the loaded values against the source. Import settings cannot determine the intended meaning of ambiguous data by themselves.

Why totals, sorting, and formulas can go wrong

Functions that expect numbers can behave differently when a range contains text. For example, SUM generally ignores text entries in referenced cells rather than adding the digits they display. That can produce a plausible-looking but incomplete total. COUNT counts numeric values, while COUNTA counts nonempty cells of any type, so comparing them can help reveal a column that mixes numbers and text.

Sorting can also expose mixed data. Excel sorts numeric values as numbers and text values as text, so a column containing both may not follow the numeric order you expect. A calculation that uses a text value directly may return an error rather than treating the characters as a quantity. After conversion, recalculate or inspect dependent formulas and totals; correcting the data type does not guarantee that every downstream result is set up as intended.

Dates are another source of confusion. Excel stores recognized dates as serial numbers and displays them using a date format. A date-looking string that Excel did not parse remains text, while a numeric serial formatted as General may look like an ordinary number. Decide whether the problem is the stored value or simply its display before changing anything.

Cases where converting to a number is the wrong fix

Not every string made of digits should be numeric. Postal codes, account numbers, product codes, and phone numbers are identifiers, not quantities. They may need leading zeros, and arithmetic on them usually has no useful meaning. Keep these values as text; otherwise, conversion can remove leading zeros or change how the identifier is stored.

Long identifiers need special care. Excel preserves only 15 significant digits of numeric precision. A longer identifier converted to a number can lose digits, even if it appears to have been entered correctly. Store long IDs as text from the start and compare them with the original source before using them in lookups or exports.

Also distinguish a genuine data problem from a display choice. If the stored value is numeric but you want two decimal places, a percentage, or a currency symbol, use a suitable number format. If it is a text string, convert or clean the value first. Choosing between those fixes prevents accidental changes to data that was already correct.

Prevent the problem on future imports

Prevention is easiest when you know what each column represents. Before pasting or importing, decide which fields are quantities and which are identifiers. Format identifier columns as Text before entering or importing them, and keep source data intact until you have checked how Excel interpreted it.

  • Preview CSV files with the import workflow instead of assuming every column will be interpreted correctly.
  • Check regional decimal and thousands separators when a file comes from another country or system.
  • Use consistent source formats and remove unwanted spaces or symbols at the source when possible.
  • Compare row counts and a few known values after conversion, then review totals or other checks that should agree with the source.

For repeated work, document the expected data type for each column. A column specification such as “quantity: decimal number” or “account code: text” helps prevent both text-number errors and the opposite mistake of converting meaningful identifiers into quantities.

Frequently Asked Questions

Why does SUM ignore numbers in some cells?
Those entries may be text rather than numeric values. Check a sample cell with ISNUMBER, inspect for spaces or separators, and convert only if the field is meant to be a quantity. SUM ignoring text can make a total incomplete without making the source cells look obviously different.

Does changing the cell format to Number convert text to numbers?
No. Number formatting changes the display of numeric values, but it does not reliably convert text strings. Use a conversion command or a suitable formula, then apply the desired display format if needed.

Why does Excel remove leading zeros?
Excel may interpret an entry as a number, and numeric values do not preserve leading zeros as part of their value. For postal codes and other identifiers, keep the column as text. If zeros are only a display requirement for a genuine number, a custom number format may show them without changing the underlying value.

Why does the conversion produce the wrong decimal value?
The source and Excel may use different decimal or thousands separators. Check the original file’s convention and use an import setting or NUMBERVALUE with the correct separators. Do not guess: interpreting 1.234 as a decimal value or a grouped integer can change its meaning substantially.

Can Excel turn a text number into a date?
Yes, Excel can interpret some entries as dates depending on their format and regional settings. If an entry is an identifier or a value that must remain unchanged, import or format that column as text before conversion and compare it with the source afterward.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.