Excel Not Converting to Number? Fix Text-Formatted Values

If Excel is not converting a value to a number, first select the affected cells and use the warning icon that appears beside them, then choose Convert to Number. If there is no warning icon, use Data > Text to Columns and finish with the General column format; this often converts numbers stored as text without changing the displayed values.

Why Excel keeps numbers as text

Excel stores numbers and text differently. A cell containing the number 125 can be used in arithmetic, sorted numerically, and formatted as currency. A cell containing the characters 1, 2, and 5 as text may look identical, but Excel can sort it among words and may ignore it in calculations such as SUM. The distinction is easy to miss because both values can appear the same on the worksheet.

Text-formatted numbers commonly arrive from a CSV file, a website, a database export, or another spreadsheet. They can also result from typing an apostrophe before a value, setting a cell to Text before entering data, or using a formula that deliberately returns text. A leading apostrophe is usually hidden in the grid, so the cell may show 125 even though Excel treats its content as text.

This is not always an error. Account codes such as 00125, postal codes, product IDs, and phone numbers are often best kept as text because their digits identify something rather than represent an amount. Converting 00125 to a number removes the leading zeros. Before changing a range, decide whether the values should be calculated or preserved exactly as identifiers.

Try the fastest conversion methods

For a small range, begin with Excel’s built-in warning. A green triangle may appear in the upper-left corner of a cell, and selecting the cell can show a warning button. Choose Convert to Number if the prompt identifies the value as a number stored as text. You can select multiple affected cells first and apply the conversion to the selection when Excel offers that option.

If no warning appears, try Text to Columns, described below. Another option is to copy a numeric 1, select the text-number cells, and use Paste Special with the Multiply operation. Multiplying by 1 converts ordinary numeric text while preserving the numeric value. Avoid applying it blindly to a large mixed range: text labels, blank cells, formulas, and identifiers may not be appropriate targets.

Use Convert to Number for a few cells when Excel shows the warning.
Use Text to Columns for a clean range of numeric text with consistent conventions.
Use a formula when you want a separate, reviewable converted column.
Clean or re-import the source when the range contains hidden characters or inconsistent separators.

Check what is actually in the cell

Before converting, test a representative cell. Select it and look at the formula bar: a leading apostrophe may reveal text entry, while a formula beginning with an equals sign may be returning text. You can also use a temporary check. With the value to inspect in A2, =ISNUMBER(A2) returns TRUE for a numeric value and FALSE for text. This check does not change the original cell.

Alignment is only a clue, not a reliable test. Numbers are commonly right-aligned and text left-aligned by default, but custom alignment can override that appearance. Similarly, changing the number format to Number is not a conversion. It changes how numeric values display; it cannot turn text characters into a number by itself.

Look for content that prevents conversion: spaces before or after the digits, a currency symbol embedded as text, a nonbreaking space copied from a web page, or punctuation used differently in the source. A value such as 1.234,56 may mean one thousand two hundred thirty-four and 56 hundredths in one regional convention, but it may not parse that way under another convention. The source and Excel’s regional settings matter.

Convert a range with Text to Columns

Text to Columns is a practical fix when the selected cells contain values that Excel can interpret consistently. On Windows in Microsoft 365, select the range and choose Data > Text to Columns. In the wizard, choose Delimited and select Next, leave the delimiter boxes unselected unless the data really uses delimiters, then select Next. Set the column data format to General and select Finish. Check the results before replacing or deleting any original data.

The General choice tells Excel to interpret values as appropriate types, including numbers. The wizard can also split data, so do not select a delimiter accidentally if your values contain commas or other separators. For a single column that contains numbers with commas as thousands separators, the source’s regional conventions determine whether conversion succeeds or whether a cleanup step is needed.

On Excel for Mac, Text to Columns is also available from the Data tab, although the ribbon layout and wizard appearance may differ by release. Excel for the web has a more limited set of desktop tools; if the command is unavailable in your web interface, open the workbook in the desktop app or use a formula-based conversion. Save a copy first when working with valuable imported data.

Use a formula for a controlled conversion

A formula is useful when you want to preserve the original text and compare the converted result beside it. With the text value in A2, enter =VALUE(A2) in B2 and fill the formula down. If A2 contains the text 125, B2 returns the numeric value 125. If the text cannot be interpreted as a number under the current settings, VALUE returns an error rather than silently fixing the source.

For ordinary spaces and common nonprinting characters, with the text in A2, try =VALUE(SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160),"")) in B2. For A2 containing the digits 125 surrounded by regular spaces or a nonbreaking space, this returns 125. TRIM removes extra ordinary spaces, CLEAN removes certain nonprinting characters, and SUBSTITUTE removes the nonbreaking space represented by CHAR(160). This will not repair every possible Unicode character or inconsistent number format, so inspect any errors.

If the formula succeeds, copy the results and use Paste Special > Values if you need fixed numbers rather than formulas. Keep the original column until the converted results have been checked. For a formula result that looks numeric but remains text, inspect the formula: TEXT intentionally returns text, and joining a number to text with the ampersand operator also produces text. Replace the text-producing logic if the result needs to participate in calculations.

Fix imported CSV and external data

Repeated conversion problems often begin during import. A CSV file contains text fields separated by delimiters; it does not carry all the formatting and type information of an Excel workbook. When Excel opens a CSV directly, it may infer types, but values with leading zeros, unusual separators, long digit strings, or mixed content can be interpreted differently than intended.

For more control in desktop Excel, use Data > From Text/CSV, review the preview, and check the detected data types before loading. If the file uses a particular decimal or thousands separator, confirm that the import settings match the source. In Power Query, inspect the applied data type step and set the column to the appropriate type. If an automatic type-detection step caused an unwanted change, adjust or remove that step before loading the data.

Be cautious with long identifiers. Excel has a limit of 15 significant digits for numeric precision; a longer account or tracking number should normally be imported as text to avoid losing digits. Likewise, importing a code such as 00073 as a number removes its leading zeros. A display format can show zeros again in some cases, but storing a code as text is often the safer choice when its exact characters matter.

Common reasons conversion still fails

Hidden spaces: A copied value may contain a nonbreaking space or another invisible character. TRIM alone removes ordinary spaces, not every special space.
Wrong decimal or thousands separator: A period and comma can have different roles across locales. Use import settings or clean the source according to its actual convention rather than replacing punctuation indiscriminately.
Currency symbols or units: A cell containing 25 USD or a symbol embedded in the text may not convert directly. Remove the label only if it is not part of the information you need to retain.
Dates that are not dates: Date-looking text depends on regional interpretation. A string such as 04/05/2026 can be ambiguous; confirm the intended month and day before converting.
Formula output is text: A formula using TEXT or concatenation can display digits while returning a text string. Fix the formula or convert its result deliberately.
Text format applied before entry: Switching the cell to General afterward does not automatically reinterpret its existing contents. Re-enter, convert, or use a conversion method.

Also distinguish a genuine zero from a blank-looking cell. A formula may return an empty string, which is text, or a cell may contain spaces. Those cells will not behave like numeric zero. If a total is wrong, inspect the contributing cells and the formula’s range rather than assuming every visible digit is a number.

Choose the safest fix for your data

The best method depends on what the values mean and how often the problem recurs. A one-time cleanup of a clean column is different from importing a monthly report with changing formats. Consider these factors before converting:

Meaning: Convert quantities, prices, and measurements that need arithmetic. Preserve IDs, postal codes, and other labels as text.
Consistency: A built-in conversion is suitable for uniform values; mixed separators or hidden characters call for inspection and cleanup.
Traceability: A helper formula keeps source and result side by side, making errors easier to find than an immediate overwrite.
Repeatability: For recurring files, correct the import or Power Query type step so the same problem does not return each time.
Precision: Do not convert long codes or identifiers to numbers if exact digits or leading zeros must survive.

After conversion, test more than one cell: check a value with a decimal, one with a thousands separator, and one near the beginning or end of the range. Use a simple calculation or ISNUMBER check to confirm that Excel now treats the results as numbers. Review totals and sort order as additional checks, but do not rely on appearance alone.

Frequently Asked Questions

Why does Excel show a number but treat it as text?
Excel can display text that consists entirely of digits, so appearance does not prove the cell is numeric. A leading apostrophe, a Text format applied before entry, imported content, or a formula that returns text can cause this. Check with ISNUMBER or use a controlled conversion.

Will changing the cell format to Number fix it?
No. Number formatting affects the display of values Excel already stores as numbers. It does not convert existing text. Convert the contents first with the warning option, Text to Columns, or a suitable formula, then apply the number format you want.

Why is the Convert to Number warning missing?
Excel does not show the warning for every text value. The error-checking setting may also be disabled, or the selected cell may not match a condition that triggers the prompt. Use ISNUMBER to check the cell, then try Text to Columns or a formula instead.

Why does SUM ignore some cells that look numeric?
SUM can ignore text values in a referenced range, even when those cells display digits. Check the cells with ISNUMBER and convert only the values intended to be amounts or quantities. Also verify that the SUM range includes the expected cells.

How do I keep leading zeros?
Keep codes such as 00125 as text when the zeros are part of the identifier. If the values are meant to be numbers but need a consistent display, use a custom number format such as 00000; however, that changes display rather than the underlying numeric value, so it is not suitable when the exact characters must be preserved in exports.

Can I convert a whole column at once?
Yes, but first confirm that the selection contains only values meant to be numeric. Select the relevant range and use Convert to Number if Excel offers it, or apply Text to Columns. For mixed data, convert a helper column first and inspect the results before replacing the source.

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.