Excel Text to Value: Convert Numbers Stored as Text

How-to basics · Updated November 18, 2026

To turn numeric text into a real Excel number, select the cells and choose Convert to Number from the warning icon, or enter =VALUE(A2) when the text is in A2. For a whole imported column, use Data > Text to Columns and finish the wizard with the column format set to General.

What “text to value” means in Excel

Excel stores information in different ways. A cell may contain the number 125 as a numeric value, or the characters 1, 2, and 5 as text. They can look identical on the worksheet, but Excel handles them differently. A genuine number can be added, sorted numerically, used in arithmetic, and evaluated by many functions. Text that resembles a number may instead sort alphabetically or be ignored by calculations.

Text-to-value conversion means asking Excel to interpret text as an underlying value. This is often needed after importing a CSV file, copying data from a website, or receiving a report from another system. A green triangle in a cell corner or a number aligned to the left can be a clue that Excel sees text, although alignment is not conclusive because formatting and alignment settings can change how cells look.

“Value” can refer to more than an ordinary number. Excel stores dates and times as numeric serial values and displays them using date or time formats. Currency symbols and thousands separators may be part of the text Excel must interpret. Conversely, an account number such as 001257 may be an identifier, not a quantity. Converting it to a number would discard the leading zeros, so the correct outcome depends on what the data represents.

Quick ways to convert a cell or column

For a small number of cells, use Excel’s error-checking option when it appears. Select a cell with a green triangle, click the warning icon beside it, and choose Convert to Number. To apply the operation to several neighboring cells, select the range first if Excel offers the option for the selection; otherwise, convert the cells individually or use a column-wide method.

For a larger imported column, Text to Columns is a practical no-formula option in Excel for Microsoft 365 on Windows:

  1. Select the cells containing the numeric text.
  2. Choose Data > Text to Columns.
  3. In the wizard, select Delimited and click Next. For values that contain no delimiter, make sure no delimiter is selected so the contents stay in one column.
  4. Click Next, leave the column data format as General, and click Finish.

General tells Excel to interpret recognizable numbers and dates rather than preserve them as text. Check the result before using this approach on mixed data: a column containing both identifiers and quantities may need different treatment by row. Text to Columns can also split fields when a delimiter is selected, so review the wizard settings rather than clicking through without checking them.

Another option is to copy a cell containing 1, select the text-number range, and use Home > Paste > Paste Special > Multiply. Multiplying numeric text by one usually coerces it to a number without changing its numeric magnitude. This is a bulk technique, but it can fail on text with unrecognized separators or extra characters. Keep an untouched copy of important source data until you have checked the converted results.

Convert with the VALUE function

The VALUE function converts text that Excel recognizes as a number, date, or time into a numeric value. With the text 1250 in A2, enter this formula in B2: =VALUE(A2). The result is the number 1250. Fill the formula down to process the rest of the rows, then, if you need to replace the original column, copy the results and paste values over the source only after checking them.

VALUE is useful when data arrives repeatedly and you want a conversion step in a worksheet rather than a manual operation. It can also make the intent clear to someone reviewing the workbook. However, the function does not magically clean arbitrary text. A value containing an unexpected unit, extra punctuation, or a separator Excel does not recognize may return #VALUE!.

For ordinary leading or trailing spaces, you can combine VALUE with TRIM. With the text 125 in A2, including spaces around the digits, enter this in B2: =VALUE(TRIM(A2)). It returns 125. TRIM removes ordinary extra spaces and reduces repeated internal spaces to single spaces; it does not remove every possible invisible character. For instance, a nonbreaking space copied from a web page may need separate cleanup.

A double unary is a shorter alternative for coercing many numeric text strings. With 125 in A2 as text, this formula in B2 returns the number 125: =--A2. It is concise, but less self-explanatory than VALUE, and it is not a cure for text that Excel cannot parse. Use whichever method makes the workbook easiest for its next user to understand.

Choose a method that fits the data

The best method depends on whether you need a one-time correction, a repeatable formula, or a conversion that preserves particular formatting. Consider these factors before changing a column:

  • One-time correction: Convert to Number or Text to Columns is usually straightforward when every entry represents the same kind of number.
  • Repeatable import: VALUE in a helper column makes the transformation visible and easy to fill down. For recurring data loads, Power Query can also be used to set a column’s data type during the import process.
  • Different regional separators: NUMBERVALUE lets you specify the decimal and group separators explicitly.
  • Identifiers or codes: Keep values such as postal codes, product codes, and account numbers as text when leading zeros or exact character sequences matter.
  • Mixed content: Separate the rows that represent numbers from labels or codes before applying a bulk conversion.

For example, an imported sales column containing 250, 400, and 125 as text should normally become numeric so it can be totaled. A product code 00125, by contrast, may need to stay text so it remains 00125. The visual similarity between the two cases does not make their meaning the same.

Decimal and thousands separators

Excel interprets text according to regional settings and the separators used in the input. A value such as 1,234.56 uses a comma for grouping and a period for decimals in one common convention. Other regions use the reverse convention: 1.234,56. If the source and Excel’s expected separators disagree, a conversion may fail or produce an unintended result. Do not assume a comma always means a thousands separator.

NUMBERVALUE is useful when you know the source convention. With the text 1.234,56 in A2, enter this formula in B2: =NUMBERVALUE(A2,",","."). The second argument identifies the decimal separator as a comma and the third identifies the grouping separator as a period; the result is 1234.56 as a number. Check that this interpretation matches the source system before filling the formula down.

If the text uses the same conventions Excel already expects, VALUE or the built-in conversion options may be enough. If separators vary between rows, a single conversion rule can be unsafe. Inspect representative examples from the beginning, middle, and end of the data, and compare the results with the original source. In a workbook shared across countries, document the convention used so another person does not reinterpret the data differently.

Dates, times, currency, and leading zeros

Excel dates are numeric values displayed with a date format. When date-like text is converted successfully, the underlying result may appear as a large number if the cell is formatted as General. That does not necessarily mean the conversion failed: select the result and apply a date format using Home > Number Format. The displayed date depends on the value Excel interpreted, so verify ambiguous dates such as 03/04/2026 against the source; that text could mean March 4 or April 3 depending on the convention.

Time text can also become a numeric fraction of a day, then display as a time when the cell uses a time format. Currency text may convert if its symbols and separators are recognized under the current settings, but imported currency labels or codes can prevent conversion. For financial records, confirm the currency and decimal convention rather than treating a successful conversion as proof that the amount was interpreted correctly.

Do not convert values to numbers just because Excel marks them as text. Leading zeros are significant in many identifiers. A ZIP or postal code, employee ID, or SKU might contain only digits but still be a label, not a quantity. Keep such data as text, or use a suitable display format if the underlying value should be numeric but must show a fixed number of digits. If Excel has already removed zeros during conversion, the original text or a known formatting rule is needed to restore them reliably.

Common conversion problems and how to fix them

A #VALUE! result usually means Excel could not interpret the complete text string under the chosen method. Check for currency symbols, units, apostrophes, nonbreaking spaces, or an unexpected decimal mark. A green triangle can indicate text that looks numeric, but a triangle is not a guarantee that every cell in the column has the same issue. Use a helper column or inspect individual examples before changing the full range.

Sometimes a cell contains a leading apostrophe used to force text entry. The apostrophe may not display as part of the cell contents, but it affects how Excel stores the entry. A conversion method can remove the text status if the remaining characters form a valid number. If the value is an identifier, leave it as text instead.

If a formula result still looks like text, check the cell’s number format and the formula’s inputs. Formatting a text value as Number does not itself convert the underlying text. Conversely, a real number displayed with a date or custom format can look unlike a conventional number. You can inspect a sample by changing its format to General, but restore the intended display format after confirming the underlying result.

For invisible characters, first identify what is present rather than applying increasingly complicated formulas. Copy a sample into a plain-text editor, compare it with a clean entry, or use Excel’s Find and Replace when the unwanted character is consistent and visible. TRIM handles ordinary spaces, while CLEAN removes many nonprinting characters; neither function removes every Unicode character. If data comes from a system export, correcting the export or import settings may be more reliable than repeatedly repairing the worksheet.

Verify the result and preserve the source

After conversion, check more than the appearance of the cells. Confirm that a representative value behaves numerically, that a small total or comparison is plausible, and that leading zeros or date interpretations have not changed. Sorting can be a useful clue: text numbers may sort alphabetically, while numeric values sort by magnitude, but sorting alone is not a complete test because mixed data can produce confusing results.

When you use a formula in a helper column, the results remain formulas until you replace them with values. To retain only the converted results, copy the helper cells and use Home > Paste > Values where appropriate. On Windows, Ctrl+Alt+V opens Paste Special; on Mac, Control+Command+V opens the corresponding dialog. The exact dialog presentation can differ by platform. Keep a backup or source column until you have verified the final data, particularly before sorting, deleting, or overwriting the original records.

Frequently Asked Questions

How do I convert text to numbers in Excel without a formula?
Select the affected cells, click the warning icon, and choose Convert to Number if Excel offers it. For a column, Data > Text to Columns with General selected is another option. Check that the entries are quantities rather than identifiers before converting.

Why does VALUE return #VALUE!?
Excel may not recognize a character or separator in the text. Look for extra spaces, nonbreaking spaces, currency symbols, units, or a decimal and grouping convention that differs from Excel’s settings. Clean or reinterpret the source text, then try again.

Does formatting a cell as Number convert text?
No. A number format changes how numeric values are displayed; it does not reliably turn text into numeric values. Use Convert to Number, Text to Columns, Paste Special multiplication, or a conversion formula for the underlying data.

How can I convert a number stored as text with a comma decimal?
If Excel recognizes the text under its regional settings, VALUE may work. Otherwise, use NUMBERVALUE and specify the source decimal and grouping separators explicitly. Verify the result with a known example before applying the method to a full column.

How do I keep leading zeros?
Keep identifiers such as postal codes and product codes as text when their zeros are meaningful. Numeric conversion removes leading zeros because they do not change a number’s value. If a numeric value must display a fixed number of digits, apply an appropriate custom number format instead, while remembering that the underlying value remains numeric.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.