How to Convert Text to Numbers in Excel? 5 Easy Methods
(Note: This guide on how to convert text to numbers in Excel is suitable for all Excel versions including Office 365.)
While using Excel, you will be dealing with lots of data in text and number formats. Sometimes texts and numbers may get accidentally intertwined, making it hard for Excel to differentiate the numbers from the text.
Excel mistaking a text for a number happens because of some functions like STRING, which converts numbers into text. There is also the possibility that when numbers are copied or downloaded from databases, some numbers might be formatted as texts.
This causes some errors and issues when using functions like SUM and ROUND since they only work on numbers. Excel throws an error in case of such exceptions. To avoid this, you need to convert the numbers formatted as texts back to numbers. Are you wondering how to do that? Don’t worry, you have come to the right place.
In this guide, you’ll learn:
Watch our video on how to convert text to numbers in Excel
Related:
How to Insert Bullet Points in Excel? 5 Easy Methods
How to Insert a Page Break in Excel? (3 Simple Steps)
How to Combine Two Columns in Excel? 2 Easy Methods
How Are Numbers Indicated as Text in Excel?
In Excel, when numbers are formatted as texts, the cells have a small green flag at the top left of the cell.
If you click on the cell, they are shown with an exclamatory indicator along with a message saying “this number is formatted as a text or preceded by an apostrophe”.
Note: Numbers that are left-aligned to the cell are text.
Also Read:
The FORMULATEXT Excel Function – 2 Best Examples
3 Best Methods to Find Duplicates in Excel
How to Autofit Excel Cells? 3 Best Methods
How to Convert Text to Numbers in Excel?
In this guide, I will show you 5 simple and easy ways to convert text to numbers in Excel.
1. Using the Dropdown
When a number bears an apostrophe before it, it is considered a text. It can be seen with a green flag towards the left top of the cell along with an exclamation mark notification. See if the exclamatory notification is available to the right of the cell.
You can easily convert a text to a number by clicking on the dropdown to the right of the cell.
Click on the dropdown and select Convert to Number.
This converts the text to a number.
Note: You can see the formatted number to the right of the cell.
2. Using Text to Column
This helps you convert multiple cells with numbers as texts.
Select the column which contains the numbers as text.
Click on Data > and select Text to Column.
Click on Finish.
This converts your text to numbers.
Note: Excel has default values necessary to convert text to numbers. So, you can just click Finish. The text to columns setup is commonly used to split columns. To split columns, select the values based on your preferences, click Next and Finish.
You can change the format of the numbers by clicking on Home > Number. From the dropdown menu, select the preference for your numbers format.
3. Using Paste Special
This is an easy method to convert the text to numbers.
Enter “1” in any of the cells.
Right-click the cell and click Copy. Or, you can use the keyboard shortcut by pressing Ctrl + C.
Now, select the cell. Right-click on the cell and select Paste Special.
This opens up a dialog box.
In the Paste Special window, select All and select Multiply. Click OK.
Since we are multiplying the text by 1, the text gets converted to a number and returns the same value.
This formats the text as a number.
Note: You can also copy the number “0”. In the dialog box, under Operation, click on Add. Click OK.
4. Using Value Function
You can convert a text to a number by using certain functions together.
Enter the function =VALUE() in the cell where you want the text to be converted as a number.
Let’s look at the example Excel sheet below. The value (55) as a text is in cell A2. To use the function, you have to specify the cell position inside the parenthesis. To convert the text in cell A2 to a number, we use the formula =VALUE(A2) in cell B2.
This converts the number into a new cell.
You can use the drag handle to apply the function to other cells.
Now once they are converted. You can copy the formatted numbers and paste them to their dedicated places by clicking on Paste Special and selecting Values.
Note: When you download any data from databases or while copying huge data from one source to another, there might be some unknown values showing up. To avoid this, you can also use the Value, Clean and Trim functions to properly convert the data.
5. Using Arithmetic Operations
By using mathematical operations on a text, you can convert text to numbers.
Operate with “0” while adding, and operate with “1” while multiplying or dividing.
To convert a text to a number using arithmetic operations. Enter the arithmetic operation in the destination cell.
For example, to convert the text in cell A3, multiplying any value by 1 gives the same value. This way, the values get converted from text to numbers.
Note: To replace the values in the original place, right-click and Copy the number. Right-click the destination cell. Under Paste Special, click on Paste Values.
Suggested Reads:
How to Format Numbers in a Microsoft Excel 2016 Spreadsheet
Combine numbers and words in Excel 2013 using the VLOOKUP Function
How to Filter in Excel? A Step-by-Step Guide
Closing Thoughts
Knowing how to convert text to numbers in Excel is essential for using some functions in Excel. This will help you avoid unnecessary confusion and errors while handling huge data. In this article, we saw 5 easy ways to convert text to numbers. You can use any one of these methods to quickly convert text to numbers and save time.
For more high-quality guides on Excel, kindly visit our resources section.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!