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:

  1. Convert text to numbers using a Smart Tag
  2. Convert text to numbers using Paste Special
  3. Convert text to numbers using Text to Columns
  4. Convert text to numbers by adding zero
  5. 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):

  1. 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.
  2. Click the Smart Tag in the first cell. A drop-down menu appears.
  3. Select Convert to Number.

Microsoft Excel Smart Tag to convert numbers stored as text to numbers.

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:

  1. Select a blank cell and enter 1.
  2. With the cell selected, press Ctrl + C or right-click and choose Copy.
  3. Select the range with the cells you want to convert to numbers.
  4. Right-click a cell in the range and select Paste Special and then Paste Special again. A dialog box appears.
  5. Click Multiply. This will multiply each cell by 1 and force the numbers stored as text to convert to numbers.
  6. Click OK.

Micrososft Excel Paste Special dialog box with Multiply selected to convert text to numbers.

3. Convert text to numbers using Text to Columns

To use Text to Columns to convert text to numbers:

  1. Select the range with the cells you want to convert to numbers.
  2. Click the Data tab in the Ribbon.
  3. Click Text to Columns. A dialog box appears.
  4. 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.

Microsoft Excel Text to Columns dialog box to convert text to numbers.

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)

MORE COURSES >

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?

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.

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

Summary
How to Convert Text to Numbers in Excel (5 Ways)
Article Name
How to Convert Text to Numbers in Excel (5 Ways)
Description
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.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This