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:
- Text values are aligned to the left side of the cell by default.
- Excel’s status bar displays the Count (number of selected cells) when selecting multiple cells with text values. Besides, the status bar displays the Average, Sum, and Count when cells contain numbers.
- The formula bar displays a leading apostrophe for the selected text-formatted cells.
- The Number Format box displays the Text format on the ribbon under the Home tab.
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:
- First, we need to select the corresponding Excel cells that consist of numbers in the form of text values.
- Next, we must click the drop-down arrow attached with the error indicator and choose the ‘Convert to Number’ option. This will instantly change or convert the text to numbers in all the selected cells.
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:
- First, we need to select the cells with text-formatted numerical values.
- Next, we must navigate the Home tab and click on the drop-down list under the Number section. It looks like this:
- Lastly, we must choose or click the ‘Number’ option from the Number format drop-down list. This will convert or display recorded text-formatted numbers as exact numbers.
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:
- First, we need to select all the desired text-formatted cells that we want to convert as numbers.
- Next, we must copy these cells using the keyboard shortcut ‘Ctrl + C’. Also, we can copy the selected cells using the right-click menu (contextual menu).
- After copying the text-formatted cells, we must select the cell where we need to paste them as numbers. We can even select the same cells. After that, we need to press the right-click button and choose the ‘Paste Special’ option. Alternately, we can use the keyboard shortcut ‘Ctrl + Alt + V’ to launch Paste Special window.
- In the Paste Special window, we must select the ‘Values’ under the Paste section and ‘Add’ under the Operation section. It will look like this:
- Lastly, we must click the OK button, and the selected cells will be treated or read by Excel as numbers.
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:
- First, we must select all the text-formatted numbers (numerical values) cells. We must ensure that the cell format (or cell type) is set to General under the Number format.
- Next, we need to navigate the Data tab on the ribbon and click the ‘Text to Columns’ button under the Data Tools section. It looks like this:
- After that, Excel displays the ‘Text to Column’ conversion wizard. In the first step of the conversion wizard, we need to select the ‘Delimited’ option under the ‘Original data type’ category.
- Lastly, we must click the Finish button. We don’t need to complete other steps of the conversion wizard. The selected cells’ values will be converted to numbers immediately.
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:
- First, we need to select the cell where we want to record the converted number from the source cell of the text-formatted number.
- Next, we must apply the VALUE function in the following way on our selected destination cell:
=VALUE(Cell Reference of Text-formatted Number)
Suppose we have a text-formatted value in a cell B2, we can apply the VALUE function in any other cell like this:
- Lastly, we must press the Enter key. This will change/ convert the Text-formatted Numbers to Numbers. In the following example image, we can see that the values are aligned to the right side of the cell after using the VALUE function, meaning that Excel now perceives them as numbers.
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
- We can choose contiguous (adjacent) or non-contiguous (non-adjacent) cells accordingly when selecting cells in Excel. If we want to select only the contiguous cells, we must hold down the Shift key while clicking the first and last cell of the desired range. If we want to select non-contiguous cells, we must hold down the Ctrl key while clicking each cell individually.
- Excel’s Paste Special feature/ tool is the most convenient way to convert text to numbers.
- We must be careful when using arithmetic operations on cells to convert text-formatted number cells to exact numbers. We must not multiply or divide the cell reference by zero; otherwise, the results will be wrong.