The tutorial shows many different ways to turn a string into a number in Excel: Convert to Number error checking option, formulas, mathematic operations, Paste Special, and more.
Sometimes values in your Excel worksheets look like numbers, but they don't add up, don't multiply and produce errors in formulas. A common reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough to convert numerical strings imported from other programs to numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how to convert strings to "true" numbers.
Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, and Excel will inform you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe.
In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of textnumbers:
Numbers  Strings (text values) 


In the image below, you can see the text representations of numbers on the right and actual numbers on the left:
There are a handful of different ways to change text to number of Excel. Below we will cover them all beginning with the fastest and easiest ones. If the easy techniques don't work for you, please don't get disheartened. There is no challenge that cannot be overcome. You will just have to try other ways.
If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a twoclick thing:
Done!
Another quick way to convert numerical values formatted as text to numbers is this:
Compared to the previous techniques, this method of converting text to number requires a few more steps, but works almost 100% of time.
To fix numbers formatted as text with Paste Special, here's what you do:
If done correctly, your values will change the alignment from left to right, meaning Excel now perceives them as numbers.
It is another formulafree way to convert text to number in Excel. When used for other purposes, for example to split cells, the Text to Columns wizard is a multistep process. To perform the text to number conversion, you click the Finish button in the very first step :)
That's all there is to it!
So far, we have discussed the builtin features that can be used to change text to number in Excel. In many situations, a conversion can be done even faster by using a formula.
Microsoft Excel has a special function to convert a string to number  the VALUE function. The function accepts both a text string enclosed in quotation marks and a reference to a cell containing the text to be converted.
The VALUE function can even recognize a number surrounded by some "extra" characters  it's what none of the previous methods can do.
For example, a VALUE formula recognizes a number typed with a currency symbol and a thousand separator:
=VALUE("$1,000")
=VALUE(A2)
To convert a column of text values, you enter the formula in the first cell, and drag the fill handle to copy the formula down the column:
For more information, please see VALUE formula to convert text to number.
Apart from textnumbers, the VALUE function can also convert dates represented by text strings.
For example:
=VALUE("1Jan2018")
Or
=VALUE(A2)
Where A2 contains a textdate.
By default, a VALUE formula returns a serial number representing the date in the internal Excel system. For the result to appear as an actual date, you just have to apply the Date format to the formula cell.
The same result can be achieved by using the DATEVALUE function:
=DATEVALUE(A2)
For more information, please see How to convert text to date in Excel.
The VALUE function also comes in handy when you extract a number from a text string by using one of the Text functions such as LEFT, RIGHT and MID.
For example, to get the last 3 characters from a text string in A2 and return the result as a number, use this formula:
=VALUE(RIGHT(A2,3))
The screenshot below shows our convert text to number formula in action:
If you don't wrap the RIGHT function into VALUE, the result will be returned as text, more precisely a numeric string, which makes any calculations with the extracted values impossible.
For more information, please see How to extract number from string in Excel.
One more easy way to convert a text value to number in Excel is to perform a simple arithmetic operation that does not actually change the original value. What can that be? For example, adding a zero, multiplying or dividing by 1.
=A2+0
=A2*1
=A2/1
If the original values are formatted as text, Excel may automatically apply the Text format to the results too. You may notice that by the leftaligned numbers in the formula cells. To fix this, be sure to set the General format for the formula cells.
That's how you convert text to number in Excel with formulas and builtin features. I thank you for reading and hope to see you on our blog next week!
10 Responses to "How to convert text to number in Excel"
I want to add text data field in rows portion after Values field in excel pivot table. How can I do that?
How to change a text into the number like we need to convert "Best" is equal to 5.
You are amazing! Saved my day and future days as well!!
Saved my day too..
There are a number of tricks you can use if you have a column of numbers in a format that Excel does not recognise as numbers. For example, let's say you have a column of numbers where some idiot has typed each number in using a comma as the thousands separator like this:
52,321.45
17,615.28
19,785.98
41,963.49
If your version of Excel does not use commas for the thousands separator, (mine is et to use the apostrophe  ' ), or if some dummy actually typed in the commas, you can still quickly convert the entire column of textnumbers to numbers by using the Data  Text to columns function:
Highlight the entire column.
Click text to numbers
Select "Delimited" and hit next.
Uncheck all the delimiters and hit next
Click General and then Advanced
In the advanced box it asks for the decimals separator and for the thousands separator.
Enter the separator you see on the screen (NOT the separator that you want). So in the above example you would put a comma as the thousands separator, and a fullstop as the decimal separator.
click OK and finish.
Hey presto your textnumbers now ARE numbers in your preferred Excel format. (with an apostrophe as the thousands separator  in my case)
Hi
My excel does not recognize the following as a value 2 027,60. I need a thousands separator to be a "," and decimal to be "." . I have a whole column of values that need to be converted.
Please help!!!
I have tried the substitute function which works on changing the decimal function for values less than a thousand, thereafter it does not work on a value of 1,000 or more.
Thanks!
ShortCut key  ALT+a+e+f
Thanks to you, column to text was causing excel to crash, this was the best solution since i am trying to automate the a script, that does just this.
Thank you so much to this very helpful post.
Thank you!