|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

VALUE

VALUE

  • Last updated on

Description

Use this function to convert a text string that represents a number to an actual number. Supported in Chains. 

This function is useful when you need to perform calculations on numbers that are stored as text.

 

Syntax

VALUE(text)

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
text Yes The text string that you want to convert to a number. A text string that represents a number (e.g., "123", "45.67", "3.14E2").

 

Examples

Sample data

  A B
1 50% 2:30PM
2 $1,000.50 four
3 12 May 2024 3/25/2025
4 Penelope345 18-July-2020

Sample formulas

Use case Formula Explanation and Result
Convert a percentage value provided as text to a number. =VALUE(A1)

This formula works in the following manner:

  • VALUE(: This is the function itself, which converts text that appears in a recognized format (like a number, date, or time) into a numeric value.
  • A1: The cell reference containing the text "50%".
  • The function interprets "50%" as the decimal value 0.5.

For this data set this formula returns 0.50.
Convert a time value provided as text to a number. =VALUE(B1)

This formula works in the following manner:

  • VALUE(: This is the function itself.
  • B1: The cell reference containing the text "2:30PM".
  • The function interprets "2:30PM" as a time and converts it to its serial number representation, which is a fraction of a 24-hour day (14.5 hours / 24 hours).

For this data set this formula returns 0.604166667.
Convert a currency value provided as text to a number. =VALUE(A2)

This formula works in the following manner:

  • VALUE(: This is the function itself.
  • A2: The cell reference containing the text "$1,000.50".
  • The function ignores the currency symbol ($) and the thousands separator (,) and converts the remaining string to a number.

For this data set this formula returns 1000.5.
Convert a number name provided as text to a number. =VALUE(B2)

This formula works in the following manner:

  • VALUE(: This is the function itself.
  • B2: The cell reference containing the text "four".
  • The function cannot interpret spelled-out words as numbers and thus returns an error.

For this data set this formula fails, and returns #VALUE!.
Convert a date provided as text to a number. =VALUE(A3)

This formula works in the following manner:

  • VALUE(: This is the function itself.
  • A3: The cell reference containing the text "12 May 2024".
  • The function recognizes the text as a date and converts it into its corresponding date serial number.

For this data set this formula returns 45424.
Multiply a text value by a number. =VALUE(A2)*2

This formula works in the following manner:

  • VALUE(A2): Converts the text "$1,000.50" in cell A2 into the number 1000.5.
  • *2: Multiplies the resulting number by 2.

For this data set this formula returns 2001.
Extract a number from a text string. =VALUE(RIGHT(A4, 3))

This formula works in the following manner:

  • RIGHT(A4, 3): Extracts the rightmost 3 characters from the text in cell A4 ("Penelope345"), resulting in the text string "345".
  • VALUE(...): Converts the text string "345" into the number 345.

For this data set this formula returns 345.

 

Notes

  • VALUE is particularly useful when you have imported or copied data that Workiva recognizes as text but you need to convert it to numbers for calculations.
  • If the text string cannot be recognized as a number, the VALUE function will return a #VALUE! error.
  • In many cases, Excel automatically converts text to numbers as needed, making the VALUE function unnecessary.
  • When dealing with currency formats, VALUE can strip out the currency symbols and convert the text to a numeric value.
  • VALUE can be useful in formulas where you need to ensure that text representations of numbers are converted to actual numeric values.

 

Tips

  • Combine VALUE with text functions such as LEFT, RIGHT, or MID, to extract numeric values from text strings.
  • Use VALUE to ensure consistency in data that may contain numeric values stored as text.
  • Consider using the TEXT, function if you need to convert numbers to text for display purposes.
  • Use conditional formatting to highlight cells that contain text representations of numbers to ensure they are properly converted using VALUE.

 

Related functions

COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
ISBLANK
ISERROR
ISNA
ISNUMBER
VALUE

Was this article helpful?
0 out of 0 found this helpful
Description Use this function to convert a text string that represents a number to an actual number. Supported in Cha...