How to use the VALUE function to convert text to number in Excel
How to use the VALUE function to convert text to number in Excel: The VALUE function is commonly used in Excel to convert text to number. Follow this article to learn more about how to use the VALUE function in Excel.
1. VALUE function structure
Function syntax: =VALUE(text)
- Text: required argument, is the text enclosed in double quotes or a reference to a cell containing the text you want to convert.
- Text can be any date, time, or constant number format, as long as it is recognized by Microsoft Excel. If the text is not in one of these formats, the VALUE function returns the error value #VALUE!.
- Usually, you do not need to use the VALUE function in a formula because Excel will automatically convert text to number when necessary. This function is provided for compatibility with other spreadsheet programs.
2. How to use the VALUE function
For example, we have a spreadsheet as shown below. We need to extract the last 3 characters of the Item code and convert it to a number.
In this case, to extract the last 3 characters of the Item code, we can use the RIGHT function. The formula to extract the last 3 characters of the Item code is as follows:
We can see that the RIGHT function returns a text value because the original format of the Item code is text. To convert the result to a number, we need to combine the VALUE function. The formula to convert the result to a number is as follows:
Copy the formula to the remaining cells, we get the results:
Therefore, the above article has introduced to you how to use the VALUE function in Excel. Hopefully, through this article, you have understood how to use this function. Wish you success!