How to Use VALUE in Excel
Excel's VALUE function solves a frustrating problem: text that looks like numbers but won't calculate. When you import data from external sources, download reports, or receive spreadsheets from...
Key Insights
- The VALUE function converts text strings that look like numbers into actual numeric values Excel can calculate with, solving common data import and formatting issues
- VALUE handles currency symbols, percentages, dates, and times automatically based on your system’s regional settings, but returns #VALUE! errors for non-numeric text
- Modern Excel often converts text to numbers automatically, making VALUE most useful when combined with text manipulation functions or when dealing with stubborn formatted data
Introduction to the VALUE Function
Excel’s VALUE function solves a frustrating problem: text that looks like numbers but won’t calculate. When you import data from external sources, download reports, or receive spreadsheets from others, you’ll frequently encounter numbers stored as text. These values appear correct but fail in formulas, refuse to sum, and generally behave like text instead of numbers.
The VALUE function converts text strings to numeric values. Its syntax is straightforward: =VALUE(text). The function takes a single argument—the text you want to convert—and returns a number Excel can use in calculations.
You’ll need VALUE most often when cleaning imported data, fixing CSV imports that treat everything as text, or processing data scraped from websites. It’s also essential when extracting numeric portions from mixed text-number strings using text functions like LEFT, RIGHT, or MID.
Basic VALUE Function Examples
The simplest use case converts plain text numbers to values:
=VALUE("123")
This returns the number 123. While this seems trivial—Excel often handles this conversion automatically—VALUE becomes crucial when automatic conversion fails.
VALUE handles formatted numbers intelligently. It recognizes and strips common formatting:
=VALUE("$1,234.56")
This returns 1234.56 as a pure number, removing the dollar sign and comma. The function works with various currency symbols based on your system’s regional settings.
Percentages convert to their decimal equivalents:
=VALUE("45%")
This returns 0.45. Excel interprets the percent sign and performs the division by 100 automatically.
Here’s a practical scenario. Suppose column A contains sales figures imported as text with currency formatting:
A1: "$1,250.00"
A2: "$3,450.75"
A3: "$890.50"
In column B, use VALUE to convert these:
B1: =VALUE(A1) → 1250
B2: =VALUE(A2) → 3450.75
B3: =VALUE(A3) → 890.5
Now you can sum B1:B3 and perform calculations that would fail with the text-formatted originals.
Working with Dates and Times
VALUE converts text-formatted dates to Excel’s internal date serial numbers. Excel stores dates as numbers counting days since January 1, 1900 (or 1904 on some Mac systems). This conversion enables date calculations:
=VALUE("1/15/2024")
This returns 45306 (assuming the 1900 date system), which Excel can format as a date. The specific serial number depends on your system’s base date.
For time strings, VALUE converts to decimal fractions of a day:
=VALUE("14:30:00")
This returns 0.604166667, representing 14.5 hours divided by 24. Format this cell as time to display “2:30 PM” or “14:30”.
Combined date-time strings work as well:
=VALUE("1/15/2024 14:30:00")
This returns 45306.604166667, the date serial number plus the time fraction.
This functionality proves useful when parsing log files or timestamps stored as text. If you have timestamps like “2024-01-15 09:30:00” in column A, VALUE converts them to proper date-time values you can sort, filter, and calculate with.
One critical caveat: VALUE interprets dates according to your system’s regional settings. “3/4/2024” means March 4th in the US but April 3rd in Europe. Always verify date conversions when working with international data sources.
Common Errors and Troubleshooting
The #VALUE! error appears when Excel cannot convert the text to a number. This happens with non-numeric text:
=VALUE("abc") → #VALUE! error
Spaces cause problems too. Leading or trailing spaces prevent conversion:
=VALUE(" 123 ") → #VALUE! error
To fix this, nest VALUE inside TRIM:
=TRIM(VALUE(" 123 "))
Actually, reverse that order:
=VALUE(TRIM(" 123 ")) → 123
TRIM removes spaces first, then VALUE converts the cleaned text.
Handle errors gracefully with IFERROR:
=IFERROR(VALUE(A1), 0)
This returns 0 if VALUE fails, preventing error propagation through your spreadsheet. Alternatively, return the original text:
=IFERROR(VALUE(A1), A1)
Regional format differences cause subtle issues. A European number “1.234,56” (using period as thousands separator and comma as decimal) won’t convert correctly on a US system expecting “1,234.56”. You’ll need to substitute characters first:
=VALUE(SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", "."))
This replaces periods with nothing and commas with periods, converting European format to US format before VALUE processes it.
Non-breaking spaces (character code 160) are invisible troublemakers. They look like regular spaces but TRIM doesn’t remove them:
=VALUE(SUBSTITUTE(A1, CHAR(160), ""))
Practical Applications and Alternatives
VALUE’s real power emerges when combined with text manipulation functions. Extract and convert portions of strings:
=VALUE(LEFT(A1, 5))
If A1 contains “12345-ABC”, this extracts “12345” and converts it to the number 12345.
Parse formatted product codes:
A1: "PROD-00123-XL"
=VALUE(MID(A1, 6, 5)) → 123
MID extracts characters 6-10 (“00123”), and VALUE converts it to 123.
Clean currency values with multiple issues:
A1: "$ 1,234.56 USD"
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "$", ""), "USD", ""), " ", ""))
This strips all non-numeric characters before conversion.
However, VALUE isn’t always necessary. Modern Excel (2016 and later) performs implicit conversion in many situations:
=A1 + 0
If A1 contains “123” as text, adding zero coerces it to a number. This works for simple cases but fails with formatted text like “$1,234.56”.
Multiplying by 1 achieves the same result:
=A1 * 1
For bulk conversions, Text-to-Columns offers a faster alternative:
- Select the text-number column
- Data → Text to Columns
- Click Finish without changing settings
Excel converts the entire column instantly. This beats copying formulas down thousands of rows.
Another approach: paste values using Paste Special. Copy blank cells, select your text-numbers, Paste Special → Add. This forces Excel to recalculate and convert text to numbers.
Best Practices and Tips
Use VALUE when you need explicit conversion that automatic coercion won’t handle. This includes:
- Text with currency symbols or thousands separators
- Percentages stored as text
- Numbers extracted from mixed strings
- Dates and times as text
Skip VALUE for simple text numbers where implicit conversion works. =A1+B1 handles text numbers fine without VALUE wrapping.
For large datasets, consider performance. VALUE in formulas across 100,000 rows recalculates every time the spreadsheet changes. If possible, convert once using Text-to-Columns or Paste Special, then work with real numbers.
Validate data before conversion. Check for unexpected characters:
=ISNUMBER(VALUE(A1))
This returns TRUE if conversion succeeds, FALSE otherwise. Use this in conditional formatting to highlight problematic cells before processing.
Create a helper column for conversions rather than replacing original data immediately. Keep the source data intact until you verify conversions worked correctly. Once confirmed, copy the converted values and Paste Special → Values over the originals.
Document your regional format assumptions when sharing workbooks internationally. A formula that works perfectly on your system might fail for colleagues in different locales.
The VALUE function remains essential for data cleaning despite Excel’s improved automatic conversion. Master it alongside text manipulation functions, and you’ll handle messy imported data with confidence. When numbers refuse to calculate, VALUE is your solution.