The VALUE function in Google Sheets is a text conversion function that transforms a text string representing a number into an actual number. This function is particularly useful when working with data that appears to be numbers but is stored as text.
Contents
Syntax
=VALUE(text)
Parameters
text– A required argument representing the text string you want to convert to a number.
Purpose of the VALUE Function
The primary purposes of the VALUE function include:
- Converting text representations of numbers into actual numeric values
- Enabling mathematical operations on text-based number strings
- Resolving #VALUE! errors caused by text-number mismatches
Conversion Capabilities
The VALUE function can convert numbers represented by strings as:
- Numeric strings
- Percentages
- Numbers with thousands separators
- Numbers with currency symbols
- Scientific notation numbers
⚠️ Google Sheets automatically converts the examples in column B to numbers when a user manually types them in, negating the need for conversion. However, numbers coming from external sources are often strings. The time output of the custom TRAVELTIME function is one example.
Examples
Make a copy of the Google Sheets file for the examples in a live sheet. Let’s discuss each line above in more depth.
Example 1: Basic Number Conversion
We’ll start with a '7 in cell B2. We use the ' before the number to force Google Sheets to treat it as text. This helps to imitate what can happen with numbers coming into your Sheet from external sources.
Input (B2): '7
Formula: =VALUE(B2)
Result: 7
Example 2: Percentage Conversion
Next, we’ll use ="75%" as the starting data. Using a = at the beginning creates a formula, and wrapping the characters in "s converts them to a string.
Input (B3): ="7%"
Formula: =VALUE(B3)
Result: 0.07
Example 3: Number with Thousand Separators
For this third example, we’ll simulate a number type with a thousands separator. Google Sheets may still recognize this as a number, so we’ll use the same technique of forcing it to be text.
Input (B4): ="1,000"
Formula: =VALUE(B4)
Result: 1000
Handling Conversion Errors
If the text cannot be converted to a number, the VALUE function will return a #VALUE! error. Common scenarios include:
- Non-numeric text
- Incomplete numeric representations
- Unexpected formatting
Best Practices
- Use the function when importing or cleaning data
- Combine with other text functions like TRIM for preprocessing
- Always validate data before conversion
- Handle potential errors with IFERROR function
Alternatives and Related Functions
- TEXT – Converts numbers to text
- TO_PURE_NUMBER – Another numeric conversion function
- ISNUMBER – Checks if a cell contains a number
Conclusion
The VALUE function is a powerful tool in Google Sheets for converting text to numbers, enabling more flexible data manipulation and analysis.