Watch our free training video on Convert Text to Numbers:
Excel is a great tool with numerous features that can streamline data management and analysis. But sometimes, you may come across situations where numbers in your spreadsheet are mistakenly categorized as text, resulting in calculation errors and formatting problems. In this article, we will explore different ways to easily convert text to numbers:
Let’s look at these methods thoroughly!
Checking if a Value is Numeric or Text
When working with data in Excel, it’s important to establish if a value is numeric or text. This information allows you to apply appropriate calculations, formatting, and data analysis techniques.
A great place to start is by looking at where the value is aligned within the cell. In Excel, text values are always aligned to the left of a cell, while numeric values are aligned to the right. However, this method is not totally foolproof as users can change the alignment of the cell values.
Another way to check is by looking at the Status Bar. As you can see, the numeric values will display the average, count and sum calculations. Whereas if the cells are in text format, it will only display the count.
In order for us to be 100% sure, we can use the ISNUMBER Function. The cell will display either TRUE if the value is numeric or FALSE if it is not.
Select a cell where you want to display the result.
Enter the formula =ISNUMBER(A2) in the selected cell, assuming the value you want to check is in cell A2.
Press Enter to apply the formula.
By using the ISNUMBER function, you can quickly determine the nature of a value in Excel and proceed with appropriate calculations and data handling techniques.
Now that we have determined if the Value is Numeric or Text, we can move on to How to Convert Text to Number.
Excel’s Built-in Convert to Number Feature
If you encounter cells displaying an error indicator (such as a green triangle in the top-left corner), converting text strings to numbers is a simple two-step process:
Select all the cells that contain numbers formatted as text.
Click on the warning sign displayed and choose the option Convert to Number.
That’s it! The text strings will be converted into numeric values, resolving any formatting issues or calculation errors.
Change the Format
Follow these steps to convert text to numbers using the Change the Format method:
Select the range of cells containing the text-formatted numbers that you want to convert.
Right-click on the selected range and choose Format Cells from the context menu. Alternatively, you can go to the Home tab in the Excel ribbon, click on the Number Format drop-down arrow, and select Format Cells.
In the Format Cells dialog box, go to the Number tab.
Choose the desired category for your numbers (e.g., “Number,” “Currency,” “Percentage”).
Specify the number of decimal places and other formatting options as needed.
Click OK to apply the new format to the selected cells.
Excel will automatically convert the text-formatted numbers into actual numeric values with the specified format. This method allows you to control the formatting style of the resulting numeric values.
Using the Paste Special
If for some reason you cannot see the green triangle on the upper left-corner of the cell and it is still showing as a text, you can follow these steps to change text to numbers using the Paste Special feature in Excel:
Enter the number 1 in an empty cell and copy it (Ctrl+C).
Select the cell(s) containing the text-formatted numbers that you want to convert.
Go up to the Home ribbon and click on the Paste drop-down arrow.
Select Paste Special.
Select Multiply and click OK.
The text-formatted numbers will now be converted to actual numeric values with the specified format.
Using Text to Columns
Follow these steps to convert text to numbers using the Text to Columns feature in Excel:
Select the range of cells containing the text-formatted numbers that you want to convert.
Go to the Data tab in the Excel ribbon.
Click on the Text to Columns button in the Data Tools group. The Convert Text to Columns Wizard will open.
In the wizard, choose the option Delimited and click Next.
Select the delimiter that separates your text values (e.g., space, comma, tab). For our case, deselect all checkboxes. Click Next.
In the Column data format section, select the General option.
Click Finish to apply the changes.
Excel will now convert the text-formatted numbers to actual numeric values using the Text to Columns feature.
Remember to make a backup of your data before applying any changes to ensure data integrity.
Convert Text to Number Using Formula
The previous methods are very useful for quick fixes. But in order to fully automate the solution, a formula may be needed. With this method, we will not change the values in the cells containing the text. Rather, we will create a numbers version in another column.
Use this formula: =RIGHT(A2,LEN(A2)-1)
The extraction of the number was achieved successfully using a formula. But the RIGHT and LEN are text functions, causing the numbers to be returned in text format.
We can use the VALUE function to transform text representations of numbers into numerical values.
=VALUE(RIGHT(A2,LEN(A2)-1))
There you have it! Being able to determine whether a value in Excel is numeric or text is important for accurate data manipulation and analysis. Be sure to use these methods into your Excel workflows next time!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
