5 Ways to Convert Numbers Stored as Text to Numbers in Microsoft Excel Worksheets
by Avantix Learning Team | Updated September 15, 2023
Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows)
There are several ways to convert text to numbers in Microsoft Excel. When data is downloaded from other programs, numbers may sometimes be stored as text and you can't perform calculations correctly on the values. Also, some users may enter a number as text by typing an apostrophe (') before the number. Numbers stored as text usually appear with a green error checking marker in the cell. You can't sum or perform many other types of calculations with text.
If you're working with large data sets, it can take a long time to convert specific cells to numbers. In this article, we'll use 5 different ways to quickly convert numbers stored as text to numbers.
Recommended article: How to Stop or Control Excel's Error Checking Markers
Do you want to learn more about Excel? Check out our virtual classroom or in-person classroom Excel courses >
In this article, we'll review 5 ways to convert text to numbers:
- Convert text to numbers using a Smart Tag
- Convert text to numbers using Paste Special
- Convert text to numbers using Text to Columns
- Convert text to numbers by adding zero
- Convert text to numbers using the VALUE function
1. Convert text to numbers using a Smart Tag
To convert numbers stored as text to numbers using a Smart Tag (this option will appear only if the error checking markers are showing in the cells):
- Select the range with the cells you want to convert to numbers. Ensure the first cell in the range contains a number stored as text. The range can include text or other numbers as well.
- Click the Smart Tag in the first cell. A drop-down menu appears.
- Select Convert to Number.
2. Convert text to numbers using Paste Special
You can also convert numbers stored as text to numbers using Paste Special. This method involves multiplying the problem cells by 1 which will convert numbers stored as text to numbers.
To convert text to numbers using Paste Special:
- Select a blank cell and enter 1.
- With the cell selected, press Ctrl + C or right-click and choose Copy.
- Select the range with the cells you want to convert to numbers.
- Right-click a cell in the range and select Paste Special and then Paste Special again. A dialog box appears.
- Click Multiply. This will multiply each cell by 1 and force the numbers stored as text to convert to numbers.
- Click OK.
3. Convert text to numbers using Text to Columns
To use Text to Columns to convert text to numbers:
- Select the range with the cells you want to convert to numbers.
- Click the Data tab in the Ribbon.
- Click Text to Columns. A dialog box appears.
- You continue through the wizard, but the easiest option is to click Finish in the first screen of the wizard. Excel will assume that you want to convert numbers stored as text to numbers with the General format.
Text to Columns can also be used to separate data into multiple columns easily.
4. Convert text to numbers by adding zero
Another way to convert text to numbers is by adding zero.
If a number stored as text is in cell A1, you could enter a formula in B1 as follows:
=A1+0
5. Convert text to numbers using the VALUE function
For simple conversions, you can also use the Value function to convert text to numbers.
If a number stored as text is in cell A1, you could enter the Value function in B1 as follows:
=VALUE(A1)
The method you use is based on your use case and preference.
This article was first published on September 17, 2016 and has been updated for clarity and content.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, JOIN our email list.
More resources
How to Insert Multiple Rows in Excel (4 Fast Ways)
10 Great Excel Keyboard Shortcuts for Filtering Data
How to Use Flash Fill in Excel (4 Ways with Shortcuts)
How to Convert Seconds to Minutes and Seconds in Excel
How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values
Related courses
Microsoft Excel: Intermediate / Advanced
Microsoft Excel: Introduction to Power Query
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Introduction to VBA (Visual Basic for Applications)
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2024 Avantix® Learning
You may also like
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Stop or Control Green Error Checking Markers in Excel
In Microsoft Excel, errors are flagged with small green marker or triangle in the upper left corner of the cell. However, these indicators display when there may be an error but is, in fact, not an error.
Excel Shortcuts to Zoom In and Out in Your Worksheets (4 Shortcuts)
There are several mouse and keyboard shortcuts you can use to zoom in and out in Excel worksheets. Some of these shortcuts are built-in and others can be created by customizing Excel Options.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca
