VALUE Excel Cell Reference: Convert Text to Numbers

If A1 contains number-like text such as 42, enter =VALUE(A1) to convert it into the numeric value 42. A cell reference by itself points to the cell’s existing contents; VALUE is useful when those contents are text that Excel needs to treat as a number.

What VALUE and a Cell Reference Mean

In Excel, a cell reference identifies a cell by its column letter and row number: A1 is the cell at the intersection of column A and row 1. When a formula refers to a cell, Excel uses the value stored there. That value might be a number, text, a date, a logical value, or the result of another formula.

VALUE is a worksheet function that converts text representing a number into a numeric value. It does not look up a different cell or extract an item from a workbook. The reference inside its parentheses tells Excel which cell’s contents to convert. For example, a cell may display 42 while actually storing the text string “42”; VALUE can convert that text so calculations and numeric sorting work as expected.

This distinction matters because a cell’s appearance is not a reliable guide to its underlying data type. Text can look like a number, and a number can be displayed with currency symbols or as a date. VALUE works on the stored content, not merely on what is visible after formatting.

How the VALUE Function Works

The function syntax is VALUE(text). The argument can be a cell reference or text supplied directly, but using a reference is usually more useful because the source can change without editing the formula. Excel attempts to interpret the text according to the number and date conventions it recognizes in the current regional settings.

For an example, suppose A2 contains the text “125”. With that layout, enter =VALUE(A2) in B2; the formula returns the number 125. The result is numeric, so Excel can use it in arithmetic, numeric comparisons, and ascending or descending numeric sorts.

If the referenced cell already contains a number, VALUE is generally unnecessary: referring to the cell is enough to use that number. VALUE does not improve precision, repair an incorrect number, or make arbitrary words numeric. If the text cannot be interpreted as a supported number, date, or time, Excel returns the #VALUE! error.

Converting Text Numbers in a Worksheet

VALUE is especially useful when data comes from a web page, a text file, an exported report, or another system that stores digits as text. A typical sign is a warning indicator beside a cell or numbers that sort unexpectedly, such as 1, 10, 100, 2. Text may also fail to behave like numbers in formulas or charts.

For a simple conversion, put the source text in A2 and use the following formula in B2. With A2 containing the text “12” and B2’s formula using the number in B2, the formula returns 36:

Assuming A2 contains text “12” and B2 contains the numeric value 3, enter this in C2; the result is 36.

=VALUE(A2)*B2

To convert a whole column, enter the formula beside the first source value and fill it down. In Microsoft Excel for Microsoft 365 on Windows, drag the fill handle at the lower-right corner of the selected formula cell, or copy the formula and paste it into the remaining destination cells. Check a few results before replacing or deleting the original source data; keeping the source column is safer when the import may need to be reviewed.

To clean ordinary leading or trailing spaces at the same time, with A2 containing text “ 42 ”, enter this formula in B2; it returns the number 42:

=VALUE(TRIM(A2))

TRIM removes ordinary extra spaces around text and reduces repeated internal spaces. It does not remove every invisible character that can appear in copied data, so a conversion error may require further cleaning.

Cell Formatting Is Not the Same as Conversion

Changing a cell’s number format changes how a numeric value is displayed; it does not reliably convert text into a number. For instance, applying a currency format to text “125” may leave the cell stored as text. Likewise, removing a currency symbol from the display does not necessarily change the underlying data type.

VALUE changes the type of the formula result, but the result cell’s formatting still controls its appearance. If a result shows several decimal places or a date-like display, adjust the destination cell’s number format. In Excel for Windows, use Home > Number to select a format, or open Format Cells for more options. The number format is a presentation choice; it does not change the underlying numeric result.

There are other ways to convert imported text, including the warning menu’s Convert to Number option when Excel flags a cell, and Data > Text to Columns for suitable columns. Those options can be convenient for one-time cleanup. A VALUE formula is often preferable when the conversion should remain visible, update automatically when the source changes, or be combined with other calculations.

Regional Settings and Number Separators

Number strings are not written the same way everywhere. Some locales use a comma for the decimal separator and a period for grouping thousands; others use a period for decimals and a comma for grouping. VALUE follows the conventions Excel recognizes for the environment, so text that converts on one computer may produce an error or an unintended interpretation on another.

When you know the separators in the source data, NUMBERVALUE lets you specify them explicitly. For example, with A2 containing the text “1.234,50”, enter this formula; it returns the numeric value 1234.5 regardless of the usual meaning of those two separators in the source locale:

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

The second argument identifies the decimal separator in the text, and the third identifies the grouping separator. This is useful for imported values with a known format, but first confirm the source convention: “1,234” can mean different amounts in different systems. NUMBERVALUE is available in current Excel versions, including Microsoft 365.

Common Errors and How to Diagnose Them

A #VALUE! result usually means the cell contains characters Excel cannot interpret as part of a number. A label such as “12 apples,” a stray letter, or an unsupported symbol will not become a number merely because it contains digits. Inspect the original cell and identify whether the text includes separators, spaces, currency marks, or other characters.

Check for hidden spaces. TRIM can handle ordinary spaces, but copied text may contain nonbreaking spaces or other invisible characters. Clean the source or use a targeted replacement if you identify the exact character.
Check the decimal and grouping marks. Confirm whether the source uses a comma or a period for decimals, then use NUMBERVALUE when you need to state those separators directly.
Check for currency symbols and units. A symbol or suffix that Excel does not recognize in that context can prevent conversion. Remove or handle it deliberately rather than assuming VALUE will discard it.
Check whether the source is already numeric. If it is, a conversion formula may add complexity without changing the result.

If invalid entries are expected and you want a readable message instead of an error, IFERROR can handle the result. With A2 containing text to convert, enter this formula in B2; it returns the converted number when possible and “Check source” when conversion fails:

=IFERROR(VALUE(A2),"Check source")

This approach makes a worksheet easier to review, but it can also hide the underlying cause of a problem. For important reports, consider displaying a clear warning and checking the original data rather than silently replacing all errors with blank cells.

VALUE Compared with Other Conversion Options

VALUE is a clear choice when a text cell represents one ordinary number or a recognizable date or time. NUMBERVALUE is better when you need to specify decimal and grouping separators. For a one-time conversion, Excel’s Convert to Number warning or Text to Columns may be faster than adding formulas.

Some arithmetic operations can coerce numeric text into a number automatically, but relying on that behavior can make a workbook harder to understand and may fail when the text contains unexpected characters. VALUE makes the intended conversion explicit. It is not the same as formatting a cell, and it is not a general-purpose text-cleaning function.

Choose based on the source and whether the conversion needs to update:

Use VALUE when a formula should convert number-like text from a referenced cell.
Use NUMBERVALUE when the source’s separators must be specified.
Use a direct conversion command when cleaning a column once and you do not need a formula-based link to the original.
Clean or parse the text first when it includes labels, symbols, or other content beyond a number.

References When Copying Formulas

In a formula such as VALUE(A2), A2 is a relative reference. When you fill the formula down one row, Excel changes the reference to A3, then A4, and so on. That is usually what you want when converting a list of source values.

An absolute reference keeps the source address fixed. With A2 containing the text “125”, enter this formula in B2; it returns 125, and copying it elsewhere continues to refer to A2:

=VALUE($A$2)

Dollar signs do not change the conversion; they control how the reference behaves when copied. Use a relative reference for a row-by-row conversion and an absolute reference when every formula must use the same source cell. In mixed references, a dollar sign can lock just a column or just a row, but that is rarely needed for a straightforward conversion column.

Frequently Asked Questions

Does VALUE convert a number that is already numeric?
It can return the numeric value, but it is usually unnecessary. If the cell already stores a number, use its reference directly. VALUE is mainly useful when the referenced content is text that represents a number.

Why does VALUE return #VALUE! for a cell that looks like a number?
The cell may contain hidden characters, unsupported symbols, or separators that do not match Excel’s regional conventions. Check the original text and try NUMBERVALUE when the source separators are known.

Does VALUE change the original cell?
No. A formula using VALUE produces its result in the cell containing the formula; it does not alter the referenced source. To replace the source with converted values, review the results first, then copy and paste values if that is appropriate for your workbook.

Can VALUE convert dates and times?
It can convert text that Excel recognizes as a date or time according to the applicable regional settings. Date and time values are stored numerically in Excel, but their displayed appearance depends on the cell’s number format. Ambiguous date text should be checked carefully.

Is VALUE the same as changing a cell to Number format?
No. Number format changes how a numeric value appears; VALUE converts supported numeric text into a numeric result. Formatting alone does not reliably change text into a number.

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.