In Excel, use the VALUE function to convert text that represents a number into a number Excel can calculate with. If the text is in cell A2, enter =VALUE(A2) in another cell; the result is numeric when Excel recognizes the text as a number, date, or time in your regional format.
What number value means in Excel
People use “number value” to mean two related things: the numeric content of a cell, and Excel’s VALUE function. A cell can look like it contains a number but actually store text. For instance, data imported from a website, downloaded as a CSV file, or copied from another system may contain the characters 125.75 as text rather than the numeric value 125.75.
This difference matters because Excel treats numeric data and text differently. Text that looks like a number may not work as expected in calculations, charts, sorting, or comparisons. A number stored as text can also trigger Excel’s green error indicator. Applying a number format changes how a value is displayed; it does not necessarily change text into a number.
To check a cell, select it and look at the formula bar and alignment. By default, numbers are usually right-aligned and text is usually left-aligned, but alignment can be changed, so it is only a clue. A function check is more dependable. With the suspected value in A2, enter =ISNUMBER(A2) in another cell. It returns FALSE if A2 contains text, even when that text looks numeric.
How the VALUE function works
VALUE takes one argument: the text you want Excel to interpret. It returns a numeric value if the text follows a format Excel recognizes under the computer’s regional settings. The function can recognize ordinary numbers and, when written in a recognizable format, dates and times. It does not perform general-purpose cleanup of every kind of imported text.
For a basic conversion, assume A2 contains the text 125.75. Enter =VALUE(A2) in B2. Excel returns the number 125.75. B2 can now be used in arithmetic, and you can apply a number format to control how the result appears. The original text in A2 remains unchanged; the formula creates a converted result in B2.
When the text is already a valid number, Excel may automatically convert it when you enter it directly into a cell. The function is especially useful when data arrives as text in a worksheet or when you need a conversion formula that updates as the source cell changes. Fill the formula down a column to convert a list, then, if needed, copy the results and use Paste Special > Values to replace formulas with fixed results.
Convert a column of text numbers
For a worksheet with imported values in A2:A100, enter =VALUE(A2) in B2 and fill the formula down through B100. Excel converts each recognized entry separately. Check a few results before relying on the whole column: one stray space, a note such as “pending,” or an unexpected separator can cause an error for an individual row.
If cells have ordinary leading or trailing spaces, you can combine conversion with TRIM. With the source text in A2, enter =VALUE(TRIM(A2)) in B2. For example, if A2 contains the text 125.75 with extra spaces around it, the formula returns 125.75. TRIM removes ordinary spaces at the beginning and end, and reduces repeated spaces between words to single spaces. It does not remove every invisible character that can occur in imported data.
For a one-time conversion, another option is Excel’s Text to Columns tool. On Excel for Microsoft 365 on Windows, select the affected column, choose Data > Text to Columns, follow the wizard, and choose Finish. This can turn text-formatted numbers into numeric cells, but inspect the results: the tool may split data if the delimiter settings are wrong, and regional number separators can affect interpretation. On Mac, the command is also available from the Data tab, though the ribbon layout can differ. Excel for the web has fewer data tools than the desktop app, so a formula is often the more portable method.
Decimal and thousands separators
A common reason conversion fails is that the text uses separators from a different region. One system may write one thousand and a half as 1,000.50; another may write it as 1.000,50. VALUE interprets text using the separators Excel expects for the current regional settings. If the source data uses different separators, Excel may return #VALUE! or read the text incorrectly.
Use NUMBERVALUE when you need to specify the separators explicitly. Suppose A2 contains the text 1.234,56, where the period separates thousands and the comma marks the decimal. Enter =NUMBERVALUE(A2,",","."). The result is 1234.56 as a number. The second argument identifies the decimal separator, and the third identifies the group separator. This is useful for files exchanged between regions because it makes the intended interpretation clear.
As with other formulas, copy the result and check it against a known source value before using it in a report or calculation. If the source includes currency symbols, unusual spacing, or other characters, test representative rows first. A conversion that succeeds is not proof that the value was interpreted correctly; for example, a wrong separator assumption can produce a valid but unintended number.
Dates, times, and leading zeros
Excel stores dates and times as numbers, then displays them using date or time formats. As a result, VALUE can convert date or time text that Excel recognizes. The displayed result depends on the destination cell’s format: a converted date may initially appear as a serial number, while a converted time may appear as a decimal. Apply an appropriate date or time format using Home > Number Format to make the result readable.
Date text is particularly sensitive to regional conventions. For example, 04/05/2026 can mean April 5 or May 4 depending on the expected order of month and day. Do not assume that a formula has interpreted ambiguous date text correctly. Confirm the source convention, use an unambiguous date format when preparing data, and compare a few converted dates with the original records.
Conversion can also remove meaningful leading zeros. If A2 contains the text 00127, =VALUE(A2) returns the number 127, not a five-character code. That is correct for a quantity but usually wrong for an account code, postal code, product identifier, or other label. Keep identifiers as text when their digits are labels rather than amounts to calculate. If you need a numeric result displayed with a fixed number of leading zeros, use a custom number format; remember that the underlying value remains numeric and the zeros are only display formatting.
VALUE versus formatting and other conversion methods
Changing a cell’s format to Number is not the same as converting text. Select a cell and choose Home > Number Format > Number to change the display format, but if the stored content is text, that action may leave it as text. A formula such as VALUE explicitly converts recognizable text, while Text to Columns can convert a selected range in place. Choose based on whether you need a repeatable formula or a one-time cleanup.
Excel may also coerce numeric text in some calculations, and typing a number into a blank cell usually creates a number automatically. These behaviors can make a problem seem inconsistent: one formula may accept a text value while another function ignores it. Explicit conversion is easier to audit than relying on implicit behavior, particularly in shared workbooks or recurring imports.
For unusual data, clean the input before conversion. Check for nonbreaking spaces, currency text, footnote marks, or thousands separators that do not match Excel’s settings. TRIM removes regular spaces but not every special whitespace character. Avoid blindly removing punctuation: a period may be a decimal marker in one file and a thousands marker in another. Understand the source format before transforming it.
Common errors and how to fix them
The most common conversion error is #VALUE!. It generally means Excel cannot interpret the supplied text as a number, date, or time in the expected format. Check for extra characters, incorrect separators, text such as “N/A,” and inconsistent date formats. If only a few rows fail, inspect those cells rather than assuming the entire column has the same structure.
A result that is a number but looks wrong often points to a formatting or interpretation issue rather than a failed formula. A date displayed as a large integer may just need a date format. A decimal that is off by a factor of 1,000 may have been interpreted with the wrong group or decimal separator. A lost leading zero is a sign that the data may be an identifier that should have stayed text.
For a reliable cleanup process, preserve the original imported column, convert into a separate column, and compare totals or sample rows before replacing anything. Keep a copy of the original file when the data is important. This makes it possible to revisit the source if a separator or date convention was misunderstood.
Frequently Asked Questions
What is the formula to convert text to a number in Excel?
Use =VALUE(A2) when the text to convert is in A2 and follows a format Excel recognizes. The result is a numeric value in the formula cell; the source cell is not changed.
Why does VALUE return #VALUE!?
Excel may not recognize the text because it contains unexpected characters, spaces, or separators that do not match regional settings. Inspect the exact source text and consider NUMBERVALUE when you need to specify decimal and group separators.
Does changing the cell format to Number convert text?
Not necessarily. Number formatting changes how a numeric value is displayed, but it may not convert text that only looks numeric. Use a conversion formula or Text to Columns, then verify the result.
What is the difference between VALUE and NUMBERVALUE?VALUE interprets text according to Excel’s regional settings. NUMBERVALUE lets you specify the decimal and group separators, making it helpful when imported data uses a different convention.
Can VALUE convert dates?
Yes, if Excel recognizes the date text under its regional settings. Because date order can be ambiguous, confirm whether the source uses month-day-year or day-month-year and format the converted result as a date.
Related
- Microsoft Excel Zoom Out: Ways to Shrink the View
- Microsoft Excel vs Apple Numbers: Which Should You Use?
- Microsoft Excel Transpose Table: Methods, Limits and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FILTER Function With Multiple CriteriaYes. In Microsoft Excel for Microsoft 365, use the FILTER function with multiplication between conditions for AND logic,…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Excel Table Array Lookup: Using VLOOKUP in a TableAn Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range…
- Excel LEN, FIND, LEFT & RIGHT: Text Extraction FormulasTo split text in Excel, combine LEN and FIND with LEFT or RIGHT: use =LEFT(A2,FIND(" ",A2)-1) to return…
- Excel Auto Round Off? What Really Happens to ValuesExcel does not normally change a number just because it displays fewer decimal places: formatting rounds the appearance,…
- Excel TRIM LEN Formula: Count Characters After CleanupTo count the characters in text after removing extra spaces, put the text in A2 and enter =LEN(TRIM(A2))…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.