Excel: From Text to Number

Excel text to number

Excel handles numbers differently than it handles text. But how do you make sure Excel actually recognizes your number as a number? Or the other way around, when you want to save a number as text on purpose. Below we explain how you can best arrange this.

Recognize if you need to do something manually

When you enter a number into a new cell, Excel will almost always recognize it as a number. To make sure it is, you can check how Excel sees the entered value on the menu bar at the top. "Default" or "Number" means that it is immediately recognized as a number. So in that case, you don't have to do anything and you can use the value in formulas, for example.

Sometimes it happens that you enter a number that is not seen as a number, but for example as text. You can see this both in the cell itself and at the top of the menu bar. In the example below you see the 100 aligned to the left and "text" in the menu bar. But it could also be, for example, a date or percentage that you want to convert. In that case you will have to manually set the value to "number".

Converting text to number

By far the easiest way to convert cells is through the selection list on the menu bar. In the example below, you see several types of cell values. By selecting all the cells and selecting "number" on the menu bar, Excel converts the values to numbers.

This gives the following result:

Excel 'Value formula'

In some cases, the above adjustment will not be sufficient. The way that always works but is slightly more cumbersome is the "Value" formula.
Normally you won't need this formula because Excel automatically recognizes the type of input. But especially when copying from other files, older Excel versions or web pages, you may need this formula.
Stand in an empty cell, and enter =value(text). In the place of "text," enter the cell where your text, date or something else is, in this case =value(b3):

Excel puts the number you are looking for in the new cell:

You can use the result in formulas and thus, for example, add it to other cells.
The advantage of this formula is that you can extend it to other cells. And when you paste a new value into cell B3, for example, the formula automatically converts it to number.

Do you run into these kinds of challenges more often and want to take Excel to the next level within your company? With modern EPM solutions like Vena, you never have to manually convert numbers or text again. All values are stored in a reliable database and immediately recognized correctly. See more about the benefits you can gain here.