3 Easy Ways to Convert Numbers Stored as Text in Excel Worksheets

by Avantix Learning Team | Updated September 16, 2016

Applies to: Microsoft® Excel® 2007, 2010, 2013 and 2016

When data is downloaded from other programs, numbers may sometimes be stored as text. Also, some users will 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 since 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 3 different ways to quickly convert numbers stored as text to numbers.

Excel’s default error checking with numbers stored as text

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

Converting numbers stored as text 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.

Converting numbers stored as text 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 Control + 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.

Converting numbers stored as text using Text to Columns

You can also use Text to Columns to convert numbers:

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

Micrososft Excel Text to Columns dialog box.

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.

Recommended Microsoft Excel training

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 classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your location on a date that’s convenient for you

To request this page in an alternate format, contact us.

Copyright 2019 Avantix® Learning Inc.

You may also like

How to Create 3D References in Microsoft Excel

You can use 3D references in Excel formulas to calculate across multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

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 Numbers Stored as Text to Numbers in Microsoft Excel
Article Name
How to Convert Numbers Stored as Text to Numbers in Microsoft Excel
Description
You can convert numbers stored as text to numbers easily in Microsoft 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 reports such as pivot tables.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This