Excel VALUE Function: Convert Text to Numbers (Examples)

Part of the free Module 5: Excel Formulas and Functions · Function 95 of 102 · Full Excel course

The Excel VALUE function converts a text string that looks like a number, date, time, currency amount or percentage into a real numeric value. It fixes the common problem of numbers stored as text after an import, which show a green triangle, align left and are ignored by SUM. VALUE reads the text using your system’s regional settings.

Syntax

=VALUE(text)

Arguments

Argument	Required / Optional	Meaning
text	Required	The text to convert, in quotes or as a cell reference. It must be in a number, date or time format that Excel recognises in the current locale.

How VALUE works

VALUE accepts any text that Excel would recognise if you typed it into a cell. =VALUE("1,234.50") returns 1234.5; =VALUE("$99") returns 99; =VALUE("15%") returns 0.15; =VALUE("04/09/2026") returns the date serial number 46269 (interpreted by the system date order); and =VALUE("3:30 PM") returns 0.645833. Leading and trailing spaces are ignored. Anything else, such as letters, a unit or a separator from another locale, returns #VALUE!. Text that is already a number is returned unchanged.

Step-by-step example: make an imported column add up

1. Column B holds amounts pasted from a PDF. They align left and =SUM(B2:B50) returns 0.
2. In C2 enter =VALUE(TRIM(B2)). TRIM removes surrounding spaces and VALUE converts the text to a number.
3. Fill down. The results align right and =SUM(C2:C50) returns the correct total.
4. Copy column C, paste as values over column B, and delete column C. Apply a number format to finish.

Practical use cases

1. Number embedded in text

=VALUE(LEFT(A2,FIND(" ",A2)-1))

Extracts the 250 from “250 units” as a real number.

2. Currency text with a code

=VALUE(SUBSTITUTE(A2,"USD",""))

3. Text time to a decimal number of hours

=VALUE(A2)*24

Converts “7:30” to 7.5 for payroll calculations.

4. Text date to a real date

=VALUE(A2)

Works when the text matches your regional date order; DATEVALUE gives the same result and makes the intention clearer.

5. Safe conversion of a mixed column

=IFERROR(VALUE(A2),A2)

Numbers stored as text become numbers; genuine text is left as it is.

Common mistakes and tips

• #VALUE! error: the text contains letters, a foreign separator such as a comma decimal point, or parentheses. Clean it with SUBSTITUTE or switch to NUMBERVALUE.
• Wrong date: “04/09/2026” is 4 September on a UK system and 9 April on a US system. Use DATE with LEFT, MID and RIGHT when the source format is fixed.
• Non-breaking spaces: TRIM does not remove CHAR(160); use SUBSTITUTE(A2,CHAR(160),””) before VALUE.
• Quick alternatives: =A2*1, =A2+0 or =--A2 also coerce text to numbers, as does Paste Special, Multiply by 1 on a whole column. The Text to Columns wizard converts in place.
• Format is not enough: changing a text cell’s number format does not convert it; the value must be re-entered or passed through VALUE.

VALUE compared with NUMBERVALUE, DATEVALUE and TIMEVALUE

VALUE is the general converter and reads numbers, dates and times in the current locale. NUMBERVALUE handles numbers only but lets you declare the decimal and thousands separators, so it is the right tool for text such as “1.234,56” from a European system. DATEVALUE and TIMEVALUE convert only the date or time part of a string; DATEVALUE ignores any time component and TIMEVALUE ignores the date, whereas VALUE keeps both in one serial number. In Excel 365 arithmetic on text numbers usually coerces them automatically, but SUM, AVERAGE and lookup functions do not, which is why an explicit VALUE step is still needed in reports and templates that receive imported data.

Related functions

• NUMBERVALUE: converts text with locale-specific separators.
• DATEVALUE: converts a text date to a serial number.
• TEXT: the reverse operation, number to formatted text.
• TRIM: removes spaces before conversion.
• See all lessons in the Excel Formulas course.

Frequently asked questions

How do I convert text to a number in Excel?

Use =VALUE(A2), or multiply the cell by 1. For a whole column, select it and use Data, Text to Columns, Finish, which converts in place.

Why does VALUE return #VALUE!?

The text contains characters Excel cannot read as a number in your locale, such as letters, a unit, or a comma used as the decimal point. Remove them with SUBSTITUTE or use NUMBERVALUE.

Can VALUE convert dates and times stored as text?

Yes. VALUE returns the serial number for recognised date and time text, so “04/09/2026 15:30” becomes a single number holding both. DATEVALUE and TIMEVALUE return the date or time part alone.

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.