Excel VALUE function to convert text to numbers

VALUE formula to convert text to number

You already know that the main purpose of the VALUE function in Excel is to change text strings to numeric values.

The following formulas give some ideas of what kind of strings can be turned into numbers:

The screenshot below shows a few more text-to-number conversions performed with the same VALUE formula:

Extract number from text string

Most Excel users know how to extract the required number of characters from the start, end or middle of a string - by using the LEFT, RIGHT and MID functions. When doing so, you have to remember that the output of all these functions is always text, even when you are extracting numbers. This may be irrelevant in one situation, but critical in another because other Excel functions treat the extracted characters as text, not numbers.

As you can see in the screenshot below, the SUM function is not able to add up the extracted values, though at first sight you may not notice anything wrong about them, maybe except for the left alignment typical for text:

In case you do need to use the extracted numbers in further calculations, wrap your formula into the VALUE function. For example:

To extract the first two characters from a string and return the result as a number:

=VALUE(LEFT(A2,2))

To extract two characters from the middle of a string beginning with the 10^th char:

=VALUE(MID(A3,10,2))

To extract the last two characters from a string as numbers:

=VALUE(RIGHT(A4,2))

The above formulas not only pull the digits, but also perform the text to number conversion along the way. Now, the SUM function can calculate the extracted numbers without a hitch:

Of course, these simple examples are mostly for demonstration purposes and for explaining the concept. In real-life worksheets, you may need to extract a variable number of digits from any position in a string. The following tutorial shows how to do this: How to extract number from string in Excel.

VALUE function to convert text to dates and times

When used on dates/times text strings, the VALUE function returns a serial number representing the date or/and time in the internal Excel system (integer for date, decimal for time). For the result to appear as a date, apply the Date format to the formula cells (the same is true for times). For more information, please see Excel date format.

The screenshot below shows possible outputs:

Also, you can use the alternative ways to convert text to dates and times in Excel:

To convert date values formatted as text to normal Excel dates, use the DATEVALUE function or other ways explained in How to convert text to date in Excel.

To convert text strings to time, use the TIMEVALUE function as shown in Convert text to time in Excel.