Microsoft Excel is a versatile tool for data analysis and manipulation. Often, we encounter situations where data in a spreadsheet contains numbers stored as text. This can lead to issues when you need to perform calculations or use functions on these values. To overcome this challenge, you will need to convert text to numbers in Excel. In this article, we will learn about 8 effective methods to achieve this and ensure your data is ready for analysis.
[8] Procedures to Convert Text to Numbers
1. Using Paste Special
While this way of turning text into numbers takes a few more steps than the others, it works almost all the time.
Steps to follow:
- Choose the cells that have text numbers and set their style to General.
- Create a copy of a new cell. Either pick out a cell and press Ctrl + C or right-click and pick Copy from the menu that comes up.
- Right-click on the cells you want to turn into numbers, and then choose “Paste Special.” You can also use the shortcut Ctrl + Alt + V.
- When you get to the Paste Special box, choose Values under Paste and Add under Operation.
- Press Ok
2. Using Text to Columns
The “Text to Columns” feature is a powerful tool for converting text to numbers, especially when dealing with large datasets.
Steps to follow:
- Select the range of cells containing the text data that you want to convert to numbers.
- Go to the “Data” tab in the Excel ribbon.
- Tap on “Text to Columns”.
- In the Text to Columns Wizard, Click on “Delimited” followed by “Next.”
- Select the delimiters that apply to your data (e.g., spaces, commas, or tabs) and configure other options if necessary.
- Click “Finish.” Excel will split the text data into separate columns, and the numeric values will be recognized as numbers.
3. Using Find and Replace
Another method for converting text to numbers is by using the “Find and Replace” feature.
Steps to follow:
- Select the range of cells containing the text data that you want to convert to numbers.
- Press Ctrl + H on your keyboard or go to the “Home” tab and click on “Find & Select,” then choose “Replace.”
- In the “Find what” category, enter a space (” “) character.
- Leave the “Replace with” field empty.
- Click “Replace All.” Excel will remove any leading or trailing spaces that might be causing the text to be treated as numbers.
4. Error Checking and Converting
Excel often automatically recognizes text numbers and flags them with a small green triangle in the upper-left corner of the cell.
Steps to follow:
- Look for green triangles in the upper-left corner of cells with text values. These triangles indicate potential errors.
- Tap on the cell which has a green triangle.
- Click the small exclamation mark icon and select “Convert to Number.”
- Excel will attempt to convert the text to a number. If it recognizes the text as a number, it will do so automatically.
5. Using the VALUE Function
The VALUE function is a straightforward way to convert text to numbers. It takes one argument (the text you want to convert).
Steps to follow:
- Open your Excel spreadsheet containing the text data that you want to convert to numbers.
- Click on an empty cell where you want the converted number to appear.
- In the formula bar, enter =VALUE(A1), where “A1” is the cell reference containing the text, you want to convert.
- Press the Enter key. The cell will now display the text value as a number.
6. Using the Double Negative (–)
It is one of the easiest and most efficient techniques for converting text to numbers.
Steps to follow:
- Open your Excel spreadsheet.
- Click on an empty cell where you want the converted number to appear.
- In the formula bar, enter =–A1, where “A1” is the cell reference containing the text you want to convert.
- Press the Enter key. The cell will now display the text value as a number.
7. Using the NUMBERVALUE Function (Excel 365 and Excel 2019)
If you are using Excel 365 or Excel 2019, you have access to the NUMBERVALUE function. This function can convert text into numbers, taking into account the regional settings, making it particularly useful when dealing with numbers in various formats.
Steps to follow:
- Open your Excel spreadsheet.
- Click on an empty cell where you want the converted number to appear.
- In the formula bar, enter =NUMBERVALUE(A1), where “A1” is the cell reference containing the text, you want to convert.
- Press the Enter key. The cell will now display the text value as a number.
8. Using the Text Function
This method involves using the TEXT function to create a new cell that contains the same number but formatted as a number, thus removing the text format. It can be useful when you want to retain the original text in one cell while displaying the number in another cell, without altering the original data in any way.
Steps to follow:
- Open your Excel spreadsheet.
- Click on an empty cell where you want the converted number to appear.
- in the formula bar, enter the following formula: =TEXT(A1, “0”), where “A1” is the cell reference containing the text you want to convert. The “0” within the double quotes indicates that you want to format the number as a plain number.
- Press the Enter key. The cell will now display the text value as a number, with the text format removed.
Conclusion
Working with text data that needs to be converted into numbers is a common task in Excel. These six methods provide you with a range of options to choose from, depending on your specific needs and the size of your dataset. Whether you prefer simple functions like VALUE or the advanced capabilities of “Text to Columns,” Excel offers versatile tools to ensure your data is in the right format for analysis, making your work more efficient and accurate. Power Query or Power Pivot can be used to import the data, and the conversion can be done in either of them. You can change texts to numbers in both of them.
