Text to Numbers in Excel
MS Excel or Microsoft Excel is the most powerful spreadsheet software with many inbuilt functions or formulas. These functions or formulas help us perform calculations, create charts, or group ranges on recorded values within the cells across multiple Excel worksheets. However, there may be cases when the values that look like numbers do not provide results as expected. They don't get calculated, cause unexpected results in charts, and produce errors in formulas.
As shown in the example sheet below, the values in cells B3 and B4 are not added by Excel:
The reason behind these calculation errors is that the values in corresponding cells are formatted as the text. Although they look like numbers, Excel reads them as text and produces errors while performing calculations on text values. In such cases, we need to change or convert all the relevant cells, which contain numbers as text values, back to numbers or numerical values.
This tutorial discusses the various step-by-step methods to convert text to a number in Excel. Before moving on, we need to understand how to identify whether a cell data/value is text or a number.
How do we know if the data is formatted as text?
Excel has a built-in error checking feature that informs about any common error occurring in the cell as soon as the respective cell is selected. In particular, if the cell is formatted as text, a small green triangle is displayed on the top-left corner of the corresponding cell. Moreover, when we select the specific cell, Excel displays a yellow error indicator with an exclamation sign. If we place the mouse cursor on this error indicator, Excel tells us about the error in the respective cell.
In the below image, when hovering the cursor on the error indicator, Excel displays an error message: "The number is this cell is formatted as text or preceded by an apostrophe." This way, we can identify the cells with text values.
Sometimes, Excel does not display an error indicator. In that case, we must check the following visual indicators for differentiating text and numbers:
In the following image, we can see the differences between text (left-side) values and the numbers (right-side):
How to Convert Text to Numbers in Excel?
When we need to perform any specific task or operation in Excel, we can use several methods. Likewise, when converting text to numbers, Excel offers multiple ways. Some methods are the fastest and easy-to-use, while others are comparatively lengthy and complex. However, we must know all the handful ways to change the text to the number because we may need to try different methods based on how the corresponding text is formatted.
The following are the most common and effective methods used to convert or change the text to numbers in Excel:
Converting Text to Number by using Excel Error Checking
When there is an error indicator (yellow sign with exclamation mark) for cells with text values, it is easy to convert text to numbers. It is only two steps process, as listed below:
Converting Text to Number by changing the Cell Format
By changing the cell format or cell type, we can easily instruct Excel to allow or display only specific number format for corresponding cells. If we set the cell format to Number only, the values entered in the respective cell will be treated as numbers or numerical values. This way, we can convert any particular values like the text to numbers.
To change the particular cell format to a Number, we must perform the following steps:
To access advanced preferences or additional settings, we can modify the cell format from the Format Cells dialogue box (Ctrl + 1). Although this method is easy-to-use, it does not always work. Suppose we first apply Text format to any specific cell, then type the desired number, and later change the corresponding cell format to Number. In that case, the specific Excel cell (s) will still be considered as text-formatted by Excel.
Converting Text to Number by using Excel Paste Special Feature
Compared to previous methods of converting text to numbers, this method involves a few more steps. However, this method works most of the time perfectly. We typically copy-paste values using Excel's Paste Special feature in this method. For this, we must perform the following steps:
Converting Text to Number by using Text to Columns
Another useful method of converting text to number involves using Excel's Text to Column feature. Typically, the Text to Column feature is used to split cells in Excel. But, this method also helps in converting text to numbers. Although the Text to Column tool consists of multiple steps, we do not need to go through all steps when using it for text to number conversion. Instead, we only need to complete the first step and finish the process in the very first step.
To perform the Text to Number conversion using Excel's Text to Column tool/ feature, we must perform the following steps:
Converting Text to Number by using Excel Formulas
Excel is best known for the availability of a wide range of built-in functions and formulas. Although there are many methods to convert text to numbers in Excel, using the formulas may be somewhat faster in many cases. In such a case, Excel's VALUE function works best for us.
The VALUE function's main advantage is recognizing the extra characters combined with a number in the corresponding cells. For instance, the VALUE function can easily recognize the number combined with a currency symbol ($) and a thousand separator. Also, it can extract the desired number from that combined or mixed text-formatted number/ value. However, we cannot use the same cells to get the numbers.
To apply the VALUE function to convert text to numbers, we must perform the following steps:
If we need to convert multiple text cells to numbers in different cells, we can copy-paste the formulas in relative cells. For non-relative cells, we can apply the formula accordingly. However, this method may not suit the need for bulk conversion across large data sets.
Converting Text to Number by using Excel Mathematic Operations
By performing simple mathematic operations within the cells, converting text to numbers in Excel can be possible. We typically perform arithmetic calculations in a way that does not change the cell value but the cell format. It is usually done by adding a zero, multiplying, or dividing by 1. For example,
=cell reference + 0
=cell reference * 1
=cell reference / 1
Excel automatically applies the number format to the corresponding cells when performing the arithmetic operations in text-formatted cell references. However, we cannot use the same Excel cells in this method. We must use the other consecutive cells to record the converted values.
In the following example sheet, we can see that text-formatted cells are left-aligned. But, when we apply arithmetic operations on these cells to their consecutive cells, the results are right-aligned, meaning that Excel now perceives them as numbers.
It is important to note that when we use this particular method of converting text to numbers, the corresponding Excel cells are treated as formulas. If we don't want Excel to read cells as formulas, we must use Excel's Paste Special feature, as discussed above in this article.
Important Points to Remember