How to use the VALUE function in Excel - A function to convert a string to a number.

When working with Excel, especially when dealing with data tables and performing calculations, Excel functions are undoubtedly indispensable tools. Each function has its own unique features, or they can be combined to help you calculate and process data tables faster.

In Excel, the VALUE function extracts a number from a string of characters in an Excel spreadsheet; simply put, it converts a string of characters into a number. This VALUE function is often combined with other functions such as LEFT, RIGHT, and MID.

How to use the VALUE function in Excel

The VALUE function has the syntax =VALUE(text) .

In this case, "text" is a string of numbers enclosed in quotation marks or a formula, referencing the cell containing the string of numbers you want to convert.

Text can be in date, time, or constant format; if the text is not in one of these formats, the VALUE function will return an error.

The VALUE function is often combined with string slicing functions such as the LEFT function slicing characters from the left, the RIGHT function slicing characters from the right, and the MID function slicing characters from the middle. When slicing strings, the formatting is not yet numerical, so the VALUE function is needed to convert it to a numerical format.

We will have the example table below. In the Employee Code column, you will filter by the last two digits of the employee code.

Step 1:

With this table, we will use the RIGHT string cutting function to extract the last two digits. The formula will be =RIGHT(B3,2) .

The result will extract two digits from the employee code sequence. These two digits will be in text format.

Step 2:

To extract the last character in the employee code sequence—specifically, only the digit 1 in the sequence, not the two digits 01—the user needs to use the VALUE function.

We have the formula =VALUE(RIGHT(B3,2)) and then press Enter.

Step 3:

We will immediately see the result returned as the number 1 in numeric format.

Scroll down to the cells below to see the remaining results. Thus, the VALUE function, combined with the RIGHT function, will help return the formatted number results.

Why does the VALUE function in Excel return the #VALUE error?

If a source string appears in a format that Excel cannot recognize, a VALUE formula will return a #VALUE error. In this case, you simply need to extract the numbers from the string in Excel.

In general, you don't necessarily need to use the VALUE function anymore because Excel automatically converts text to numbers when needed. However, VALUE is provided for compatibility with other spreadsheet applications.

1. Use the VALUE function to convert the input text into a numerical value.
2. The VALUE function converts text displayed in an easily recognizable numerical format (e.g., numbers, dates, or times) into a numerical value.
3. Excel usually automatically converts text to numerical values ​​when needed, so you don't need the Value function.
4. The Value function is provided for compatibility with other spreadsheet programs.
5. The VALUE function returns #VALUE! when a conversion fails.

Good luck with your project!