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

by Avantix Learning Team | Updated June 23, 2020

Applies to: Microsoft® Excel® 2007, 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 3 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

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.

Related

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

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 2020 Avantix® Learning Inc.

You may also like

How to Delete a Pivot Table in Excel

How to Delete a Pivot Table in Excel

A pivot table can be deleted in an Excel workbook in several ways. You can delete a pivot table, convert a pivot table to values or clear data and customizations from a pivot table to reset it. When a pivot table is created from source data in a workbook,

How to Change Commas to Decimal Points and Vice Versa in Excel (5 Ways)

How to Change Commas to Decimal Points and Vice Versa in Excel (5 Ways)

Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently.

How to Group by Month and Year in a Pivot Table in Excel

How to Group by Month and Year in a Pivot Table in Excel

You can group by month, year or other date period in a pivot table in Excel. One option is to group by date periods in a pivot table using the Grouping feature. Alternatively, you can also create calculations in source data to extract the month name and the year from a date field and use the fields in your pivot table.

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