5 Ways to Convert Numbers Stored as Text to Numbers in Microsoft Excel Worksheets

by Avantix Learning Team | Updated April 11, 2021

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows)

There are several ways to convert text to numbers in Microsoft Excel worksheets. When data is downloaded from other programs, numbers may sometimes be stored as text. 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. This is problematic because 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.

Normally, numbers stored as text are marked with the green error checking marker by default.

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 live classroom Excel courses >

1. Converting text to numbers using a Smart Tag

You can 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. Converting 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)

3. Converting 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

4. Converting 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:

  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.

5. Converting text to numbers using Text to Columns

You can also 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.

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 Change Commas to Decimal Points in Excel and Vice Versa (5 Ways)

How to Convert Seconds to Minutes and Seconds in Excel

How to Delete a Pivot Table in Excel

Related courses

Microsoft Excel: Intermediate / Advanced

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 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some live 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 training on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

How to Convert Cm to Inches in Excel (or Inches to Cm)

How to Convert Cm to Inches in Excel (or Inches to Cm)

You can convert cm to inches (or inches to cm) using formulas with operators or functions in Excel. This is useful since the regional settings on a computer affect the default measurement system used in Excel and other programs.

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | 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
You can convert text to numbers easily in Excel. Check out these 3 ways to convert numbers stored as text to numbers so you can sort the numbers correctly and use them in formulas or pivot tables.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This