You can convert text to numbers in Excel using the VALUE function. This function takes a text value as an argument and returns a number. For example, the following formula returns the number 3:
=VALUE("3")
If you have a column of text values that you want to convert to numbers, you can use the VALUE function in an array formula. An array formula is a formula that performs calculations on more than one value at a time. To enter an array formula, you press CTRL+SHIFT+ENTER instead of just ENTER when you finish the formula.
For example, suppose you have a column of text values in cells A1:A5, as shown in the following figure. These values were entered as text, not numbers, so they don't sort correctly.
To convert these text values to numbers, you can use the following array formula:
=VALUE(A1:A5)
Note:
Be sure to enter this formula as an array formula by pressing CTRL+SHIFT+ENTER.
The VALUE function converts only the first character of each cell to a number. If the first character in a cell can't be converted to a number (for example, if it's a letter), VALUE, returns the #VALUE! error value.
"For example , if your data is in cells A1 : A5 and you want to convert it into numeric values in cells B1 : B5 , you can use this TEXTTONUMBER function : = TEXT ( A1 : A5 , "# , ##0 " )." (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA )