Excel VALUE Function – Convert Text To Value

Many a time, while working with numbers in excel, it keeps the numbers in a text format itself. This usually happens when we extract and import data from some external sources into excel, and the numbers become text. At this point, it is very important to have a feature in excel which can convert a number in text format into a proper number (value) format. Interestingly, excel has come up with the VALUE Function in Excel.

In this tutorial, we would learn how to convert number in text format into actual number format.

This tutorial covers how to use VALUE formula in excel, function syntax and arguments, and examples of excel VALUE formula.

Here we go 😎

When To Use VALUE Function in Excel

The VALUE excel function is a part of text function group, which is used to convert the number in text format into actual number format.

Background

While you use any of the text functions on a number in excel, it always returns the number as a text string.

The number returned as a text string is not actually a number. Therefore, you cannot perform any of the mathematical operations on it. It is simply a text.

Look at the examples below:

In the above examples, we have used two text functions, the TEXT function and the CONCATENATE function to return a number output (column B).

You would notice that the formula output in column B is not an actual number. It is aligned left of the cell.

In this case, the VALUE function is very useful to convert numbers as text string into number format.

Syntax and Arguments

=VALUE(text)

The VALUE excel function has a single argument.

• text – In this mandatory argument, enter the text formatted number that you want to convert to number format.

The VALUE formula accepts both – the cell reference and result of any other text formula.

Examples of VALUE Function in Excel

Let’s take the same example learned in the background section above.

As learned, the numbers 0001524 and 123456 are in text format.

Let’s now use the VALUE function for converting text into number format. To achieve this, simply use the below formula:

=VALUE(B2)

AND

=VALUE(B3)

As a result, excel would convert the text string into numbers, as shown in the below image:

As a result of using the VALUE function, the numbers are now right-aligned and in proper number format.

In the above example, we have used the cell reference of B2 and C2 in the VALUE function.

Instead of using the cell reference, we may even use the output some formula directly as an input argument of VALUE formula. See the example below:

=VALUE(TEXT(1524,"0000000"))

OR

=VALUE(CONCATENATE("123","456"))

In the above examples, the TEXT and CONCATENATE function returns the text output (0001524 and 123456, respectively). This resultant text output is converted by the VALUE function into number format.

This brings us to the end of the tutorial on how to use the VALUE excel formula.

Thank You 🙂